Today’s content
2. Spring JDBC: JDBC TemplateCopy the codeDatabase connection pool
1. Concept: It is actually a container (collection) that holds database connections. When the system is initialized, the container is created, the container will apply for some connection objects, when the user to access the database, from the container to obtain the connection object, after the user access, the connection object will be returned to the container. Benefits: 1. Saving resources 2. Efficient user access 3. DataSource javax.sql Method: * getConnection: getConnection() * return Connection: connection.close (). If the Connection object Connection is fetched from the Connection pool, then the connection.close () method is called and the Connection is no longer closed. C3P0: database connection pool implementation technology, provided by Alibaba. C3P0: database connection pool implementation technology * procedure: 1. C3p0-0.9.5.2. jar McHange-commons-java-0.2.12.jar, * Do not forget to import database driver jar 2. Name: c3P0.properties or c3P0-config. XML * Path: Just place the file in the SRC directory. 3. Create the core object database connection pool object ComboPooledDataSource 4. GetConnection: getConnection * code: //1. DataSource ds = new ComboPooledDataSource(); Conn = ds.getConnection(); Druid: Database connection pool implementation technology Jar package druid-1.0.9.jar 2. Define configuration file: * is in the form of properties * can be called any name, can be placed in any directory 3. Load the configuration file. DruidDataSourceFactory = DruidDataSourceFactory = DruidDataSourceFactory Properties pro = new Properties(); InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties"); pro.load(is); / / 4. Access to the connection pool object DataSource ds = DruidDataSourceFactory. CreateDataSource (pro); Conn = ds.getConnection(); 1. Define a class JDBCUtils 2. Provide static code block loading configuration file, initialize connection pool object 3. Obtain a connection from the database connection pool. 2. Release resources. 3. Public class JDBCUtils {//1 DataSource private static DataSource ds; Static {try {//1. Properties pro = new Properties(); pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties")); / / 2. Get the DataSource ds = DruidDataSourceFactory. CreateDataSource (pro); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); Public static Connection getConnection() throws SQLException {return ds.getConnection(); public static Connection getConnection() throws SQLException {return ds.getConnection(); Public static void close(Statement STMT,Connection conn){/* if(STMT! = null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn ! = null){ try { conn.close(); } catch (SQLException e) {e.printstackTrace (); } }*/ close(null,stmt,conn); } public static void close(ResultSet rs , Statement stmt, Connection conn){ if(rs ! = null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(stmt ! = null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn ! = null){ try { conn.close(); } catch (SQLException e) {e.printstackTrace (); }} /** * public static DataSource getDataSource(){return DataSource; }}Copy the codeSpring JDBC
* The Spring framework's simple encapsulation of JDBC. Provides a JDBCTemplate object to simplify the STEPS of JDBC development: 1. Import the JAR package. 2. Create a JdbcTemplate object. JdbcTemplate = new JdbcTemplate(ds); 3. Call the JdbcTemplate method to perform CRUD operations * update(): execute the DML statement. QueryForMap (): query result encapsulates result set as map, column name as key, value as value encapsulates record as map * Note: The result set of this method can only be 1 * queryForList(): the result of the query encapsulates the result set as a list collection * Note: Encapsulate each record as a Map, and load the Map into the List. * Query (): the result of the query, encapsulating the result as arguments to the JavaBean object * query: RowMapper * In general we implement the class using BeanPropertyRowMapper. * New BeanPropertyRowMapper< type >(type.class) * queryForObject: query result, encapsulate the result as an object * Generally used for aggregate function queries 4. Exercise: * Requirement: 1. Change the salary of no. 1 to 10000 2. Delete the newly added record. 4. Query the record whose ID is 1 and encapsulate it as a Map set. Query all records and encapsulate them as a List. 6. Query all records and encapsulate them as a List of Emp objects. Import cn.itcast.domain.emp; import cn.itcast.utils.JDBCUtils; import org.junit.Test; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import java.sql.Date; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import java.util.Map; Public class JdbcTemplateDemo2 {//Junit unit test, can let the method execute independently //1. Private JDBCTemplate template template = new JDBCTemplate (jdbCutils.getdatasource ()); @test public void test1(){// 4. SQL = "Update EMp set salary = 10000 WHERE ID = 1001"; SQL int count = template.update(SQL); System.out.println(count); } @test public void test2(){String SQL = "insert into emp(id,ename,dept_id) values(? ,? ,?) "; Int count = template.update(SQL, 1015, "SQL ", 10); System.out.println(count); @test public void test3(){String SQL = "delete from emp where id =?" ; int count = template.update(sql, 1015); System.out.println(count); } /** * 4. Select * from Map where id = 1001 @test public void test4(){String SQL = "select * from emp where id =? or id = ?" ; Map<String, Object> Map = template. QueryForMap (SQL, 1001,1002); System.out.println(map); //{id=1001, ename= Sun Wukong, job_id=4, MGR =1004, JoinDate =2000-12-17, salary=10000.00, Bonus = NULL, dept_id=20}} /** * 5. List */ @test public void test5(){String SQL = "select * from emp"; List<Map<String, Object>> list = template.queryForList(sql); for (Map<String, Object> stringObjectMap : list) { System.out.println(stringObjectMap); */ @test public void test6(){String SQL = "select * from Emp "; List<Emp> list = template.query(sql, new RowMapper<Emp>() { @Override public Emp mapRow(ResultSet rs, int i) throws SQLException { Emp emp = new Emp(); int id = rs.getInt("id"); String ename = rs.getString("ename"); int job_id = rs.getInt("job_id"); int mgr = rs.getInt("mgr"); Date joindate = rs.getDate("joindate"); double salary = rs.getDouble("salary"); double bonus = rs.getDouble("bonus"); int dept_id = rs.getInt("dept_id"); emp.setId(id); emp.setEname(ename); emp.setJob_id(job_id); emp.setMgr(mgr); emp.setJoindate(joindate); emp.setSalary(salary); emp.setBonus(bonus); emp.setDept_id(dept_id); return emp; }}); for (Emp emp : list) { System.out.println(emp); */ @test public void test6_2(){String SQL = "select * from Emp "; List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class)); for (Emp emp : list) { System.out.println(emp); @test public void test7(){String SQL = "select count(id) from emp"; Long total = template.queryForObject(sql, Long.class); System.out.println(total); }}Copy the code