Spring learning path: simple practice of jdbcTemplate, springjdbctemplate

Spring learning path: simple practice of jdbcTemplate, springjdbctemplate
I. Introduction

Hibernate is a heavy packaging of jdbc, users do not feel the SQL operation, use HSQL; mybatis is a medium packaging of jdbc, still need to write some SQL, the essence is the SQL assembly; jdbcTemplate is a lightweight assembly. It only wraps the connection and shutdown of the database, but is not more intelligent than mybatis (because it fails to automatically set the query results to the model class ), let alone hibernate.

This exercise starts from the initial form of jdbctemplate and gradually approaches the form used in real development practice. The details are as follows:

Connect to the database using DriverManagerDataSource

Database Operations (add, delete, and modify)

Using spring to manage jdbcTemplate reduces the amount of code

Using the C3P0 data pool to manage connections: DriverManagerDataSource is not used in the actual working environment, because the connection must be enabled for each query, and the efficiency is too low; in actual development, you must consider changing the database. Therefore, the best way is to store the database connection information independently in an attribute file.

Support for logging and adding jdbcDAOSupport: Although jdbcTemplate can be used directly for database operations, few such operations are performed in most dao implementations, the DAO subclass implements both the DAO interface and inherits an abstract class of jdbcTemplate. The automatic injection process should be handed over to a unified class for processing. Therefore, a JdbcDAOSupport class is defined. However, this class is not only responsible for processing objects of the jdbcTemplate class, you can also take charge of automatic database shutdown, that is, if you want your development database to be closed automatically, you must inherit the jdbcdaosupport class.

Ii. Exercises

(1) Use DriverManagerDataSource to connect to the database

    public static DriverManagerDataSource getDBManager()throws Exception{        DriverManagerDataSource dbManager=new DriverManagerDataSource();// org.springframework.jdbc.datasource.DriverManagerDataSource
        dbManager.setDriverClassName("oracle.jdbc.driver.OracleDriver");
        dbManager.setPassword("tiger");

 dbManager.setUrl("jdbc:oracle:thin:@localhost:1521:orcl"); dbManager.setUsername("scott"); Connection conn=dbManager.getConnection(); System.out.println(conn); return dbManager; }

(2) complete database CRUD operations

First understand the JdbcTemplate class:

• • • Constructor: in addition to the no-argument constructor, you can also directly input a data Source: public JdbcTemplate (DataSource data );
    • Set the data Source: public void setDataSource (DataSource data );
    • Common update operations: public int update (String SQL, Object... args );
    • Update and obtain the id: public int update (PreparedStatementCreator psc, KeyHolder generatedKeyHolder) throws DataAccessException;
    • Query all: public <T> List <T> queryForList (String SQL, Object [] args, RowMapper <T> rowMapper) throws DataAccessException.
    • Query a single column: public <T> List <T> queryForList (String SQL, Object [] args, Class <T> elementsType) throws DataAccessException.
    • Query a single data: public <T> queryForObject (String SQL, Object [] args, Class <T> requiredType) throws DataAccessException.

The following describes how to use these methods:

1. Common update operations

    public static boolean update() throws Exception {        JdbcTemplate template=new JdbcTemplate();        template.setDataSource(getDBManager());        String sql="update myemp set job=? where empno=?";        int count=template.update(sql,"CXY",7369);        return count>0;    }

2. Update and obtain the id

// Update and obtain the id. The id should be self-incrementing public static void updateAndGetID () throws Exception {JdbcTemplate template = new JdbcTemplate (); template. setDataSource (getDBManager (); final String SQL = "update emp set job =? Where empno =? "; KeyHolder key = new GeneratedKeyHolder (); int count = template. update (new PreparedStatementCreator () {@ Override public PreparedStatement createPreparedStatement (Connection conn) throws SQLException {PreparedStatement pstmt = conn. prepareStatement (SQL); pstmt. setString (1, "CKW"); pstmt. setInt (2, 7369); return pstmt ;}, key); System. out. println ("the current id is:" + key. getKey (); if (count> 0) System. out. println (" Execution successful! "); Else System. out. println (" execution failed! ");}

3. query all. You need to set it manually when setting it to vo. This is troublesome. You can use reflection to set it automatically.

// Query all operations. query all data in the database. public static void getAll () throws Exception {JdbcTemplate template = new JdbcTemplate (); template. setDataSource (getDBManager (); String SQL = "select empno, ename, job, mgr, hiredate, sal, comm, deptno from myemp where rownum <=? "; List <Emp> list = template. query (SQL, new RowMapper <Emp> () {@ Override public Emp mapRow (ResultSet rs, int rowCount) throws SQLException {System. out. println ("Returned current data row:" + rowCount); Emp emp = new Emp (); emp. setEmpno (rs. getInt ("empno"); emp. setEname (rs. getString ("ename"); emp. setComm (rs. getInt ("comm"); emp. setDeptno (rs. getInt ("deptno"); emp. setHiredate (rs. getDate ("hiredate"); emp. setJob (rs. getString ("job"); return emp ;}, 10); System. out. println (list );}

4. query a single column

// Query the public static void demo5 () throws Exception {JdbcTemplate template = new JdbcTemplate (); template. setDataSource (getDBManager (); String SQL = "select empno from myemp where rownum <=? "; List <Integer> list = template. queryForList (SQL, new Object [] {5}, Integer. class); System. out. println (list );}

5. query a single data

// Query the public static void demo6 () throws Exception {JdbcTemplate temp = new JdbcTemplate (); temp. setDataSource (demo1 (); String SQL = "select count (*) from myemp where rownum <=? "; Int I = temp. queryForObject (SQL, new Object [] {8}, Integer. class); System. out. println (I );}

(3) Use Spring to manage JdbcTemplate

Simulate the business layer implementation, as shown below, using the bean configured in xml for injection:

@Componentpublic class DAOImpl implements IDAO {    private JdbcTemplate jt;    @Autowired　　    public DAOImpl(JdbcTemplate jt){        this.jt=jt;    }        @Override    public boolean doCreate(Emp emp) {        String sql="update myemp set job=? where empno=?";        int count=jt.update(sql,emp.getJob(),emp.getEmpno());        return count>0;    }}

 

Configure the bean of DriverManagerDataSource and the bean of JdbcTemplate in xml.

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>        <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>        <property name="username" value="scott"/>        <property name="password" value="tiger"/>    </bean>    <bean id="jdbcTamplate" class="org.springframework.jdbc.core.JdbcTemplate">        <property name="dataSource" ref="dataSource"/>    </bean> 

 

(4) use c3p0 to manage jdbc connections

First create database. properties

db.driver=oracle.jdbc.driver.OracleDriverdb.url=jdbc:oracle:thin:@172.16.237.129:1521:orcldb.user=scottdb.password=tigerpool.max=10pool.min=10pool.init=5pool.idle=5

Replace the configuration of exercise (3) in xml:

<Context: property-placeholder location = "classpath: database. properties"/> <! -- Read the property file. The path is in classpath and can be read under src in the exercise --> <bean id = "dataSource" class = "com. mchange. v2.c3p0. comboPooledDataSource "> <property name =" driverClass "value =" $ {db. driver} "/> <property name =" jdbcUrl "value =" $ {db. url} "/> <property name =" user "value =" $ {db. user} "/> <property name =" password "value =" $ {db. password} "/> <! -- Maximum number of available connections --> <property name = "maxPoolSize" value = "$ {db. max}"/> <! -- Minimum number of persistent connections --> <property name = "minPoolSize" value = "$ {db. min}"/> <! -- Initialize connections --> <property name = "initialPoolSize" value = "$ {db. init}"/> <! -- Maximum waiting connection --> <property name = "maxIdleTime" value = "$ {db. password} "/> </bean> <bean id =" jdbcTamplate "class =" org. springframework. jdbc. core. jdbcTemplate "> <property name =" dataSource "ref =" dataSource "/> </bean>

(5) support for JdbcDaoSupport

Rewrite DAOImpl to the following code. The red part shows the difference from the previous one:

@Componentpublic class DAOImpl extends JdbcDaoSupport implements IDAO {        @Autowired    public DAOImpl(JdbcTemplate jt){        super.setJdbcTemplate(jt);    }        @Override    public boolean doCreate(Emp emp) {        String sql="update myemp set job=? where empno=?";        int count=super.getJdbcTemplate().update(sql,emp.getJob(),emp.getEmpno());        return count>0;    }}

 

 

 

 

Summary and problems:

Although JdbcTemplate has a lot of manual writing exercises (2) code, it can be used for testing in projects without modifying the content in xml to make it messy.

The "Update and retrieve id" function in exercise (2) does not have a good idea of what id is obtained.

 

Reprinted, please describe the source ~