JDBC

JDBC( Java Data Base Connectivity ) is one of the technique of JavaEE.

How to use JDBC to query the data in the table and print the data in the console via java?

　　1. Register the sql driver

　　2. Get the connection with database

　　3. Create the sql statement object

　　4. Execute the sql statement

　　5. If you execute the query statement, you should handle the result

　　6. Close the resource

　　for exmaple:

　　　　

　　

public void test() throws Exception{

　　// register the driver
   //DriverManager.register(new com.mysql.jdbc.Driver());  ----------> not recommended
　　Class.forName("com.mysql.jdbc.Driver()");
　　// get the connection of database
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15","root","root");
　　// create statement object
    Statement statement = connection.createStatement();  
　　// execute the sql statement      
    ResultSet results = statement.executeQuery('SELECT * FROM users');
　　// get the information in the table
  while(results.next()){
    String id = results.getObject("id");
    String name = results.getObject("name");    
    String password = results.getObject("password");    
    String email = results.getObject("email");    
    String birthday = results.getObject("birthday");        
    }    
　　// close the resource
    results.close();
    statement.close();
    connection.close();
}

DriverManager:

　　function:

　　　　1. register the driver:

　　　　　　Class.forName("com.mysql.jdbc.Driver");

　　　　2. get the connection:

　　　　　　Connection connection = DriverManager.getConnecion("jdbc:mysql://localhost:3306/mydb1","root","root");

　　　　　　　　　　

　　　　　　three ways to get the connection:

　　　　　　　　method1:

　　　　　　　　　　DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1","root","root");

　　　　　　　　method2:

　　　　　　　　　　Properties prop = new Properties();

　　　　　　　　　　prop.put("user","root");

　　　　　　　　　　prop.put("password","root");

　　　　　　　　　　DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1",prop);

　　　　　　　　method3:

　　　　　　　　　　DriverManager.getConnection("jdbc:mysql://localhost:3306?user=root&password=root");

Connection: represents the database's connection, all the operations between client and datase are completed via this object.

　　get the Statemtn object via Connection:

　　Statement statement = connection.createStatement();

Statement: the sql statement object:

　　ResultSet set = statement.executeQuery(String sql);  ----> the sql must be query statement

　　int num = statement.executeUpdate(String sql);  -----> the sql must be DML statement( INSERT UPDATE DELETE ) or the

　　　　　　　　　　　　　　　　　　　　　　　　　　　　  no-return-value DDL statement; the return value is the affected row

　　boolean flag = statement.execute(String sql);  -----> execute the sql statement, if the return value is ResultSet, the flag is true, else is false

ResultSet: the database result's set, returned by the sql query statement.

　　boolean flag = ResultSet.next();  -----> move the cursor to the next position, the return value: has the result or not

　　boolean flag = ResultSet.previous();  -----> move the cursor to the previous position, the return value: has the result or not

　　boolean flag = ResultSet.absolute(int row);  -----> move the cursor to the specific position 

　　void beforeFirst();  -----> move the cursor to before the first result

　　void afterLast();  -----> move the cursor to the next the last result

　　Object getObject(int fieldIndex);  -----> get the field by index

　　Object getObject(String fieldName);  -----> get the field by the field's name

Close the resource:

　　try{...}

　　catch(){...}

　　finally{

　　　　if(resultSet!=null){

　　　　　　try{

　　　　　　　　resultSet.close();

　　　　　　}catch(SQLException e){

　　　　　　　　e.printStackTrace();

　　　　　　}

　　　　　　resultSet = null;

　　　　}

　　　　if(statement != null){

　　　　　　try{

　　　　　　statement.close();

　　　　　　}catch(SQLException e){

　　　　　　　　e.printStackTrace();

　　　　　　}

　　　　　　statement = null;

　　　　}

　　　　if(connection != null){

　　　　　　try{

　　　　　　　　connection.close()

　　　　　　}catch(SQLException e){

　　　　　　　　e.printStackTrace();

　　　　　　}

　　　　　　connection = null;

　　　　}

　　}

　　

JdbcUtil:

package com.pp;

import java.io.IOException;
import java.io.InputStream;
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 {
    private static String classDriver;
    private static String url;
    private static String user;
    private static String password;

    static {

        try {
            ClassLoader loader = JdbcUtil.class.getClassLoader();
            InputStream in = loader
                    .getResourceAsStream("dataBaseConfig.properties");
            Properties prop = new Properties();
            prop.load(in);
            classDriver = prop.getProperty("classDriver");
            url = prop.getProperty("url");
            user = prop.getProperty("user");
            password = prop.getProperty("password");
        } catch (IOException e) {
            throw new ExceptionInInitializerError(
                    "Failed to get the information of data base config file!");
        }
        try {
            Class.forName(classDriver);
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError("Failed to load the driver!");
        }
    }

    public static Connection getConnection() {
        try {

            Connection connection = DriverManager.getConnection(url, user,
                    password);
            return connection;

        } catch (Exception e) {
            throw new RuntimeException("Fail to get the connection!");
        }
    }

    public static void release(Connection connection, Statement statement,
            ResultSet set) {

        if (set != null) {
            try {
                set.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            set = null;
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            statement = null;
        }

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            connection = null;
        }
    }
}

dataBaseConfig.properties:

classDriver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb1
user=root
password=root

 If you copy a web project, don't forget to change the Web Context Root in the project's properties:

SQL Injection:

　　"SELECT * FROM user WHERE username='abc' and password='' or 1=1 or username='' ;"

　　When you input this SQL statement, you will find this statement has the equal effect of "SELECT * FROM users; ". So it's dangerous.

PreparedStatement: is the prepared statemen object, it has hign effeciency.

　　Advantages:

　　　　1. avoid SQL injection

　　　　2. you can use the placeholder to represents the value you want to insert

　　　　3. could improve the efficiency

public void addUser(User user) {
        Connection connection = null;
        PreparedStatement pstatement = null;

        try {
            connection = JdbcUtil.getConnection();
　　　　　　　　// prepare the statement and we can see the placeholder in the sql statement
            pstatement = connection
                    .prepareStatement("INSERT INTO user(username,password,email,birthday) VALUES(?,?,?,?);");
　　　　　　　　// set the value of placeholder
            pstatement.setString(1, user.getUsername());
            pstatement.setString(2, user.getPassword());
            pstatement.setString(3, user.getEmail());
            pstatement.setDate(4, new java.sql.Date(user.getBirthday()
                    .getTime()));
　　　　　　　　// execute the sql statement and return it has ResultSet or not
            boolean flag = pstatement.execute();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.release(connection, pstatement, null);
        }
    }

Singelton Pattern & Use Singelton pattern to decoupling the service and dao layer:

package pp.com.util;

import java.io.InputStream;
import java.util.Properties;

import pp.com.dao.UserDao;

public class BeanFactory {
    private static BeanFactory instace = new BeanFactory();

    private BeanFactory() {
    }

    public static BeanFactory getInstance() {
        return instace;
    }

    public UserDao getUserDao() {
        try {
            InputStream in = BeanFactory.class.getClassLoader()
                    .getResourceAsStream("dao.properties");
            Properties prop = new Properties();
            prop.load(in);

            String userDao = prop.getProperty("userDao");
            UserDao instance = (UserDao) Class.forName(userDao).newInstance();
            return instance;
        } catch (Exception e) {
            throw new RuntimeException("Fail to create the instace of UserDao!");
        }
    }
}