Comparison of Spring's jdbcTemplate with original jdbc's DBUtils and Hibernate integrating c3p0

Using User as the Operating Object

package com.swift.jdbc;

public class User {

    private Long user_id;    
    private String    user_code;    
    private String    user_name;    
    private String    user_password;    
    private String    user_state;
    
    public User() {
        super();
        // TODO Auto-generated constructor stub
    }
    public User(Long user_id, String user_code, String user_name, String user_password, String user_state) {
        super();
        this.user_id = user_id;
        this.user_code = user_code;
        this.user_name = user_name;
        this.user_password = user_password;
        this.user_state = user_state;
    }
    public Long getUser_id() {
        return user_id;
    }
    public void setUser_id(Long user_id) {
        this.user_id = user_id;
    }
    public String getUser_code() {
        return user_code;
    }
    public void setUser_code(String user_code) {
        this.user_code = user_code;
    }
    public String getUser_name() {
        return user_name;
    }
    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }
    public String getUser_password() {
        return user_password;
    }
    public void setUser_password(String user_password) {
        this.user_password = user_password;
    }
    public String getUser_state() {
        return user_state;
    }
    public void setUser_state(String user_state) {
        this.user_state = user_state;
    }
    @Override
    public String toString() {
        return "User [user_id=" + user_id + ", user_code=" + user_code + ", user_name=" + user_name + ", user_password="
                + user_password + ", user_state=" + user_state + "]";
    }
    
}

Original JDBC

Note that ResultSet is a result set with a pointer. The pointer begins to point to the previous (first) element of the first element. Unlike iterator, which has hasNext() and next(), it has only next().

package com.swift.jdbc;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DemoJDBC {

    public static void main(String[] args) throws ClassNotFoundException, SQLException, PropertyVetoException {

        findAll();
        User user=findById(9l);
        System.out.println(user);
        user.setUser_name("HanMeimei");
        update(user);
    }

    private static void update(User user) throws PropertyVetoException, SQLException {

        ComboPooledDataSource dataSource=new ComboPooledDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/crm");
        dataSource.setDriverClass("com.mysql.jdbc.Driver");
        dataSource.setPassword("root");
        dataSource.setUser("root");
        dataSource.setMinPoolSize(5);
        dataSource.setMaxIdleTime(2000);
        Connection con= dataSource.getConnection();
        String sql="update sys_user set user_name='"+user.getUser_name()+"' where user_id="+user.getUser_id();
        PreparedStatement statement = con.prepareStatement(sql);
        statement.executeUpdate();
        System.out.println("ok");
        
    }

    private static User findById(long id) throws ClassNotFoundException, SQLException {

        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/crm","root","root");
        Statement statement = con.createStatement();
        ResultSet rs = statement.executeQuery("select * from sys_user where user_id="+id);
        User user=null;
        while(rs.next()) {
            user=new User(rs.getLong("user_id"),rs.getString("user_code"),
                    rs.getString("user_name"),rs.getString("user_password"),
                    rs.getString("user_state"));
        }
        return user;
        
    }

    private static void findAll() throws ClassNotFoundException, SQLException {

        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/crm?user=root&password=root");
        PreparedStatement statement = connection.prepareStatement("select * from sys_user");
        ResultSet rs = statement.executeQuery();
        List<User> list=new ArrayList<>();
        while(rs.next()) {
            Long id = rs.getLong("user_id");
            String code=rs.getString("user_code");
            String name=rs.getString("user_name");
            String password=rs.getString("user_password");
            String state=rs.getString("user_state");
            User user=new User();
            user.setUser_code(code);
            user.setUser_id(id);
            user.setUser_password(password);
            user.setUser_name(name);
            user.setUser_state(state);
            list.add(user);
        }
        for(User u:list) {
            System.out.println(u);
        }
        rs.close();
        connection.close();
    }

    
}

DBUtils Integrating c3p0

c3p0 integrates database Connection to provide faster Connection

package com.swift.c3p0;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {

    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
    private static ThreadLocal<Connection> thread=new ThreadLocal<>();
//    static {
//        try {
//            dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/crm");
//            dataSource.setDriverClass("com.mysql.jdbc.Driver");
//            dataSource.setUser("root");
//            dataSource.setPassword("root");
//            dataSource.setMinPoolSize(5);
//        } catch (PropertyVetoException e) {
//            e.printStackTrace();
//        }
//    }
    
    public static DataSource getDataSource() {
        return dataSource;
    }
    
    public static Connection getConnection() {
        Connection con = thread.get();
        if(con==null) {
            try {
                con = dataSource.getConnection();
                thread.set(con);
                con=thread.get();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return con;
        
    }
    public static void main(String[] args) {
        Connection con = C3P0Utils.getConnection();
        System.out.println(con);
    }
}

And it can load xml configuration files directly, better than (DBCP connection pool, which only supports properties files)

<c3p0-config>
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/crm</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">root</property>
    </default-config>
</c3p0-config>

Query Runner, the killer of DBUtils

You can use? Yes, place malicious attention, and also provide an auto-add''symbol. This should be noted.

package com.swift.dbutils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.swift.c3p0.C3P0Utils;
import com.swift.jdbc.User;

public class DemoQueryRunner {

    private static QueryRunner qr=new QueryRunner(C3P0Utils.getDataSource());
    static List<User> users=new ArrayList<User>();
    
    public static void main(String[] args) throws SQLException {
        //Find all
        users=findAll();
        for(User user:users) {
            
            System.out.println(user);
        }
        //adopt Id lookup
        User user=findById(9l);
        System.out.println(user);
        //To update
        user.setUser_name("Mei Mei Han");
        System.out.println("===================================");
        System.out.println(user);
        update(user);
        
    }

    private static void update(User user) throws SQLException {

        int update = qr.update(
                "update sys_user set user_name=? where user_id=?",user.getUser_name(),user.getUser_id());
        System.out.println(user.getUser_name()+update);
        
    }

    private static List<User> findAll() throws SQLException {
        List<User> users = qr.query("select * from sys_user ", new BeanListHandler<User>(User.class));
        return users;
    }

    private static User findById(long l) throws SQLException {

        User user = qr.query(
                "select * from sys_user where user_id=?",new BeanHandler<User>(User.class),l);
        return user;
        
    }

}

Hibernate has a core configuration file

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
    <!-- Constraint in hibernate-core-5.0.7.Final.jar Of org.hibernate Under bag hibernate-configuration-3.0.dtd Search in file -->
<hibernate-configuration>
    <session-factory>
    <!-- Configuration method:data\hibernate-release-5.0.7.Final\project\etc\hibernate.properties -->
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/crm</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">root</property>
        
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        
        <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
        <property name="hibernate.c3p0.max_size">10</property>
        <property name="hibernate.c3p0.min_size">5</property>
        <property name="hibernate.c3p0.timeout">5000</property>
        
        <property name="hibernate.hbm2ddl.auto">update</property>
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.format_sql">true</property>
          
        <property name="hibernate.connection.isolation">2</property>
        
        <!-- Get the current session Put it in the current thread -->
        <!-- <property name="hibernate.current_session_context_class">thread</property> -->
        
        <mapping resource="com/swift/hibernate/User.hbm.xml"/>
    </session-factory>
</hibernate-configuration>

<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
This is to use Hibernate's own c3p0 function, do not need to write c3p0.xml file, but in addition to the need for c3p0 jar package, also need Hibernate's own jar package, can be found in the hibernate package lib - > optional - > c3p0,
Both jars in this folder: c3p0-0.9.1.jar and hibernate-c3p0-4.2.1.Final.jar are used, otherwise they will be abnormal.
Could not instantiate connection provider [org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider]

There is also a mapping file for the entity class

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Constraint in hibernate-core-5.0.7.Final.jar Of org.hibernate Under bag hibernate-mapping-3.0.dtd Search in file -->
<hibernate-mapping>
    <class name="com.swift.hibernate.User" table="sys_user">
        <id name="user_id" column="user_id">
            <generator class="native"></generator>
        </id>
        <property name="user_code" column="user_code"></property>
        <property name="user_name" column="user_name"></property>
        <property name="user_password" column="user_password"></property>
        <property name="user_state" column="user_state"></property>
    </class>
</hibernate-mapping>

Connecting to the database is through session

package com.swift.hibernate;

import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class DemoHibernate {

    static List<User> users=new ArrayList<User>();
    public static void main(String[] args) {

        users=findAll();
        for(User user:users) {
            
            System.out.println(user);
        }
    }
    private static List<User> findAll() {
        
        //new Configuration()Is created Configuration Class object, call inside configure Method, read the configuration information, and the method returns Configuration type
        Configuration config=new Configuration().configure();
        SessionFactory sf = config.buildSessionFactory();
        Session session = sf.openSession();
        Query query = session.createQuery("from User");
        users= query.list();
        session.close();
        return users;
    }

}

Posted by mkoga on Tue, 05 Feb 2019 07:12:17 -0800

Programmer Group

Comparison of Spring's jdbcTemplate with original jdbc's DBUtils and Hibernate integrating c3p0

Hot Keywords