Driver Interface

Driver The function of interface is to define some capabilities that database driven objects should have . For example, establish a connection with the database
Support for all defined methods java All databases connected by language implement the interface , The class that implements this interface is called
It is a database driver class . In the program to connect to the database , Must pass first JDK The reflection mechanism loads the database driver
class , Instantiate it . Different database driver classes have different class names .

load MySql drive ：Class.forName(“com.mysql.jdbc.Driver”);
load Oracle drive ：Class.forName(“oracle.jdbc.driver.OracleDriver”);
DriverManager class
DriverManager Drive objects through instantiated databases , Be able to establish connection between application and database
Pick up . And back to Connection Database connection object of interface type .

Common methods

• getConnection(String jdbcUrl, String user, String password)
  This method accesses the database through url、 Users and passwords , Returns the name of the corresponding database Connection object .
• JDBC URL: When connecting to the database , Identifier used to connect to the specified database . stay URL The type of the database is included in 、
  Address 、 port 、 Library name and other information . Connection of different brand databases URL Different .

Connection

• Connection Interface :Connection Connection to database （ conversation ） object . We can execute... Through this object sql Statement and returns the result
  fruit .

 Connect  MySql  database ：
Connection conn = DriverManager.getConnection("jdbc:mysql://host:port/database", "user", "password");
 Connect  Oracle  database ：
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@host:port:database", "user", "password");
 Connect  SqlServer  database ：
Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://host:port;
DatabaseName=database", "user", "password");

Common methods

• createStatement()： Create send to database sql Of Statement Object of interface type .
• preparedStatement(sql) ： Create send precompile to database sql Of PrepareSatement Interface type
  object .
• prepareCall(sql)： Create the... That executes the stored procedure CallableStatement Object of interface type .
• setAutoCommit(boolean autoCommit)： Set whether transactions are automatically committed .
• commit() ： Commit a transaction on a link .
• rollback() ： Roll back transactions on this link .

Statement Interface

For execution static SQL Statement and returns the object for which it generated the result .
from createStatement establish , For sending simple SQL sentence （ Dynamic binding is not supported ）.

Common methods

• execute(String sql): Execute... In parameters SQL, Returns whether there is a result set .
• executeQuery(String sql)： function select sentence , return ResultSet Result set .
• executeUpdate(String sql)： function insert/update/delete operation , Returns the number of updated rows .
• addBatch(String sql) ： Put more than one sql Statement in a batch .
• executeBatch()： Send a batch of sql Statement execution .

PreparedStatement Interface

Inherited from Statement Interface , from preparedStatement establish , Used to send... With one or more parameters SQL
sentence .PreparedStatement Object ratio Statement Objects are more efficient , And it can prevent SQL Inject , therefore
We usually use PreparedStatement.

Common methods

• addBatch() Put the present sql Statement is added to a batch .
• execute() Execute the current SQL, Returned boolean value
• executeUpdate() function insert/update/delete operation , Returns the number of updated rows .
• executeQuery() Execute the current query , Returns a result set object
• setDate(int parameterIndex, Date x) To current SQL Bind a... At the specified position in the statement java.sql.Date
  value .
• setDouble(int parameterIndex, double x) To current SQL Bind a... At the specified position in the statement double
  value
• setFloat(int parameterIndex, float x) To current SQL Bind a... At the specified position in the statement float value
• setInt(int parameterIndex, int x) To current SQL Bind a... At the specified position in the statement int value
• setString(int parameterIndex, String x) To current SQL Bind a... At the specified position in the statement String value
• setObject(int parameterIndex,Object o) Commonly used , Compatible with almost all entity class field properties

ResultSet Interface

ResultSet Provides a way to retrieve different types of fields .

Common methods

• getString(int index)、getString(String columnName)
  Get in the database yes varchar、char Data objects of the same type .
• getFloat(int index)、getFloat(String columnName)
  Get in the database yes Float Type of data object .
• getDate(int index)、getDate(String columnName)
  Get in the database yes Date Data of type .
• getBoolean(int index)、getBoolean(String columnName)
  Get in the database yes Boolean Data of type .
• getObject(int index)、getObject(String columnName)
  Get any type of data in the database .

ResultSet How to scroll the result set

• next()： Move to next line .
• Previous()： Move to previous line .
• absolute(int row)： Move to specified row .
• beforeFirst()： Move resultSet Foremost .
• afterLast() ： Move to resultSet

Encapsulates a JDBC Tool class

This tool class contains connection Connection pool 、, And will query 、 Modification and other operations for encapsulation , And will resultSet Mapping to entity classes , There is no need for manual operation in the user area to return the result .

public   class  JDBCUtil {
    private static final Integer SIZE = 30;
    private static String driver;
    private static String jdbcUrl;
    private static String username;
    private static String userpassword;
    private static Queue<Connection> queue = null;
    static {
        ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
        driver = bundle.getString("driver");
        jdbcUrl = bundle.getString("jdbcUrl");
        username = bundle.getString("username");
        userpassword = bundle.getString("userpassword");
        try {
            Class.forName(driver);
            queue = new LinkedList<Connection>();
            for(int i=0; i<SIZE;i++) {
                Connection connection = DriverManager.getConnection(jdbcUrl, username, userpassword);
                queue.add(connection);
            }

        } catch (ClassNotFoundException var2) {
            var2.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static Connection getConnection(){
        if(queue.size()!=0){
            return queue.poll();
        }
        try {
            return DriverManager.getConnection(jdbcUrl,username,userpassword);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     *  Encapsulation lookup api Encapsulate the found results as list The collection is returned to the user 
     * @param con
     * @param sql
     * @param parameters
     * @param returnType
     * @param <T>
     * @return
     */
    public static <T> List<T> queryList(Connection con, String sql, Object[] parameters, Class<T> returnType){
        try {
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            if(parameters!=null && parameters.length!=0) {
                for (int i = 1; i <= parameters.length; i++) {
                    preparedStatement.setObject(i,parameters[i-1]);
                }
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            List<T> list = mappingType(resultSet, returnType);
            preparedStatement.close();
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return null;
    }
    public static int executeUpdate(Connection connection,String sql,Object[] parameters){
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            if(parameters!=null && parameters.length!=0){
                for (int i = 1; i <= parameters.length; i++) {
                    preparedStatement.setObject(i,parameters[i-1]);
                }
            }
            int i = preparedStatement.executeUpdate();
            preparedStatement.close();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return -1;
    }

    /**
     *  take resultSet Mapping to entity class objects 
     * @param rs
     * @param type
     * @param <T>
     * @return
     * @throws SQLException
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     */
    private static <T> List<T> mappingType(ResultSet rs,Class<T> type) throws SQLException, IllegalAccessException, InstantiationException, NoSuchMethodException, InvocationTargetException {
        List<T> list = new ArrayList<T>();
        Field[] fields = type.getDeclaredFields();
        while(rs.next()){
            T instance = type.newInstance();

            for(Field field: fields){
                String fieldName = field.getName();
                field.setAccessible(true);
                System.out.println(" Return method ：set"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1));
                Method method = type.getMethod("set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1), field.getType());
                try {
                    method.invoke(instance, rs.getObject(fieldName));
                }catch (SQLException e){
                    System.out.println(" The return set does not have this column ");
                }
            }
            list.add(instance);
        }
        return list;
    }


    public static void closeResource(Connection connection){
        queue.add(connection);
    }
    public static void closeAll(){
        for(Connection con:queue){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}