(Import the driver jar package for this database before connecting to it, for example MySQL is mysql-connector-java-5.1.46.jar)

About the database connection pool c3p0 jar package that connects to the database:

c3p0-0.9.5-pre1.jar dependent mchange-commons-java-0.2.4.jar itcast-tools-1.4.jar (where a JdbcUtils tool class is used to connect)

Configuration file: c3p0-config.xml (name must be fixed to this under src path)

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- This is the default configuration information -->
    <default-config> 
        <!-- Connect four parameter configurations -->
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/customers</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">123</property>
        <!-- Pool parameter configuration -->
        <property name="acquireIncrement">3</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">2</property>
        <property name="maxPoolSize">10</property>
    </default-config>
    
    <!-- Specially for oracle Provided configuration information -->
    <named-config name="oracle-config"> 
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/lisi</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">123</property>
        <property name="acquireIncrement">3</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">2</property>
        <property name="maxPoolSize">10</property>
    </named-config>

</c3p0-config>

Used to connect to the database;

After connecting to the database, you need to operate on database 1:

The simplest jar package for manipulating databases in the javaweb phase is commons-dbutils-1.4.jar

The main method used is QuerRunner class with query method and update method update This is common

After connecting to the database, you need to operate on the database 2:commons-dbutils-1.4.jar depending on itcast-tools-1.4.jar.

Upgrade to TxQueryRunner (at itcast-tools-1.4.jar) after extension

Note that this TxQueryRunner is a required jar package for an extended class, commons-dbutils-1.4.jar, which is required in itcast-tools-1.4.jar

This method inside already helps you get incoming connections so you don't need to get them manually, and you don't need to close the connections so that they can be returned to the connection pool. It also handles the closure of transactional and transactional connections. See the TxQueryRunner source code for more details

So just pass in the sql template and parameters to show a piece of code:

//Registered Users (Add Users)
    public void add(User user){
        try{
        //sql Template
        String sql ="insert into tb_user values(?,?,?,?,?,?)";
        Object[] params ={user.getUid(),user.getUsername(),user.getPassword(),user.getEmail(),user.getCode(),user.isState()};
         qr.update(sql, params);
        }catch(SQLException e){
            throw new RuntimeException(e);
        }
    }
    //Find User with Activation Code
    public User findByCode(String code){
        try{
        //sql Template
        String sql ="SELECT*FROM tb_user where code=?";
        
        return qr.query(sql, new BeanHandler<User>(User.class),code);
        }catch(SQLException e){
            throw new RuntimeException(e);
        }
    }

There are many result set processors in the query method: BeanListHandler BeanHandler, which maps the data queried to a collection of objects and object returns, MapListHandler, ScalarHandler, and so on.

Start with these and add them later

Introduces a class with two methods:

CommonUtils is a tool class extended from beanutils that belongs to itcast-tools-1.4.jar

So the jar packages that this class depends on are: commons-beanutils-1.8.3.jar, commons-logging-1.2.jar, itcast-tools-1.4.jar

CommonUtils.uuid() This method randomly generates 32 characters

CommonUtils.toBean(map,clazz) This lass object that passes into a map collection and javabean and makes a one-time assignment Note that the key of the map collection should be assigned one-time corresponding to the attribute name inside javabean

query(sql,rsh, Object...param) about assigning QueryRunner at a time Similar In this case RSH is the value BeanListHandler BeanHandler used by the result set processor to load the database (these two can map the queried data into a collection of objects and object return this is also the field inside the database to be aware ofName and JavaBean property names need to be the same so that they can be mapped!!!

Code Display:

//Find users by user name
    public User findByUsername(String username){
        try{
        //sql Template
        String sql ="SELECT*FROM tb_user where username=?";
        
        return qr.query(sql, new BeanHandler<User>(User.class),username);
        }catch(SQLException e){
            throw new RuntimeException(e);
        }
    }
    //Press email Query Users
    public User findByEmail(String email){
        try{
        //sql Template
        String sql ="SELECT*FROM tb_user where email=?";
        
        return qr.query(sql, new BeanHandler<User>(User.class),email);
        }catch(SQLException e){
            throw new RuntimeException(e);
        }
    }