Summary

JDBC

JDBC access database steps

1. Load a Driver driver Driver
2. Create database connection
3. Create Sql command transmitter Statement
4. Send Sql command through Statement and get the result
5. Processing result
6. Close database resources
   • ResultSet
   • Statement
   • Connection

jdbc common interface

1. Statement interface

The object used to execute a static SQl statement and return the results it generates

Three Statement classes

1. Statement: used to send simple Sql statements
2. PreparedStatement can send statements with parameters, which is more efficient than the previous one, and can prevent SQL injection,
3. CallableStatement: inherited PreparedStatement, used to call stored procedures

• Using PreparedStatement first can avoid Sql injection risk

Common Statement

• execute run statement, return whether there is result set
• Eecutquery runs the select statement and returns the ResultSet result set
• executeUpdate runs the inset/update/delete operation to return the number of updated rows

transaction management

• In JDBC, transaction operations are committed automatically

• The system calls commit automatically, otherwise it calls rollback

• Instead, you can manually submit and call setAutoCommit (False) to disable autocommit

Time type

1. java.util.Date

• Subclass: java.sql.Date represents year day
• Subclass: java.sql.Time represents time, minute and second
• Subclass: java.sql.TimeStmp represents month day hour minute second

Date comparison processing

• Insert random date
• Retrieve records for the specified date range

Text storage

CLOB

• Used to store a large amount of text data
• Large fields are special, and different database processing methods are different. The operation of large fields is often handled in the way of flow,

Related types in mysql

• TINYTEXT
• TEXT[(M)]
• MEDIUMTEXT
• LONGTEXT
• The above four kinds of capacity increase in turn

BLOB

• Use of binary large objects
• Large field data is usually in the form of stream, rather than general field, and the data can be read out at one time

Related types in Mysql

• TINYTEXT
• TEXT[(M)]
• MEDIUMTEXT
• LONGTEXT
• The above four kinds of capacity increase in turn

Summary of tools

First kind

package com.pratice.jdbc;



/**
 * @version : 1.0
 * @auther : Firewine
 * @Program Name: <br>
 * @Create : 2018-09-29-21:30
 */


import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * The tool class for operating JDBC, in which some tool methods are installed separately
 *
 */
public class JDBCTools {
    public static void release(ResultSet rs, Statement statement, Connection conn){
        if (rs != null){
            try{
                rs.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        if (statement != null){
            try{
                statement.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        if (conn != null){
            try{
                //It's just going back to the connection pool. It's not really closed

                conn.close();
            }catch (Exception e2){
                e2.printStackTrace();
            }
        }

    }
    /**
     * Method to close statement and connection
     * @param statement
     * @param conn
     */
    public static void release(Statement statement,Connection conn){
        if (statement != null){
            try{
                statement.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        if (conn != null){
            try{
                conn.close();
            }catch (Exception e2){
                e2.printStackTrace();
            }
        }
    }
    /**
     * 1.How to get a connection
     * Get a connection from the database server by reading the configuration file
     * @return
     * @throws Exception
     */
    private static DataSource dataSource = null;
    //The database connection pool is initialized only once.
    static {
        dataSource = new ComboPooledDataSource("helloc3p0");
    }
    public static Connection getConnection()throws Exception{

        return dataSource.getConnection();
    }
//    public static Connection getConnection()throws Exception{
//        //1. Prepare 4 strings to connect to the database
//        //2. Get the input stream corresponding to jdbc.properties
//        Properties ppop = new Properties();
//
//        //3. Load the corresponding input stream
//        InputStream in =
//                JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
//
//        //4. Determine the user. Four strings of password
//        ppop.load(in);
//        //3. Load the database Driver (the corresponding Driver implementation class has the static code block of registered Driver)
//        String user = ppop.getProperty("user");
//        String password = ppop.getProperty("password");
//        String jdbcUrl = ppop.getProperty("jdbcUrl");
//        String driver = ppop.getProperty("driver");
//
//        Class.forName(driver);
//        //4. Get database connection through GetConnection method of DriverManager
//        return DriverManager.getConnection(jdbcUrl,user,password);
//    }
}

The second method

package sxt.study.jdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtil {

	static Properties pros = null;   //Can help read and process information in resource files
	
	static {   //Called when the JDBC util class is loaded
		pros = new Properties();
		try {
			pros.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	
	public static Connection getMysqlConn(){
		try {
			Class.forName(pros.getProperty("mysqlDriver"));
			return DriverManager.getConnection(pros.getProperty("mysqlURL"),
					pros.getProperty("mysqlUser"),pros.getProperty("mysqlPwd"));
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public static Connection getOracleConn(){
		try {
			Class.forName(pros.getProperty("oracleDriver"));
			return DriverManager.getConnection(pros.getProperty("oracleURL"),
					pros.getProperty("oracleUser"),pros.getProperty("oraclePwd"));
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public static void close(ResultSet rs,Statement ps,Connection conn){
		try {
			if(rs!=null){
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(ps!=null){
				ps.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(conn!=null){
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void close(Statement ps,Connection conn){
		try {
			if(ps!=null){
				ps.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(conn!=null){
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public static void close(Connection conn){
		try {
			if(conn!=null){
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	
}

C3P0 profile

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

    <named-config name="helloc3p0">

        <!-- Specify the basic properties of the connection data source -->
        <property name="user">root</property>
        <property name="password">pwd</property>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///runoob?useSSL=true</property>

        <!-- If the number of connections in the database is insufficient, How many connections are applied to the database server at a time -->
        <property name="acquireIncrement">5</property>
        <!-- Number of connections when initializing the database connection pool -->
        <property name="initialPoolSize">5</property>
        <!-- Minimum number of database connections in the database connection pool -->
        <property name="minPoolSize">5</property>
        <!-- Maximum number of database connections in the database connection pool -->
        <property name="maxPoolSize">10</property>

        <!-- C3P0 Database connection pool can maintain Statement Number of -->
        <property name="maxStatements">20</property>
        <!-- Each connection can be used at the same time Statement Number of objects -->
        <property name="maxStatementsPerConnection">5</property>

    </named-config>

</c3p0-config>