jdbc Connection Database Instance Details

Introduction to JDBC

JDBC is called Java Data Base Connectivity (java Database Connection), which provides all-in-one access to many databases.JDBC is a set of database access programming interface developed by sun and is an API of SQL level.It is written in the java language, so it has good cross-platform characteristics. Database applications written with JDBC can run on any platform that supports java without having to write different applications on different platforms.

JDBC programming steps

(1) Loading drivers:

Download driver package: http://dev.mysql.com/downloads/connector/j/

Unzip to get the jar file.Copy the file to the Java project directory Java Resources/Libraries/buildpath.

(2) Get a database connection

(3) Create an Statement object:

(4) Send SQL command to database

(5) Processing returns from databases (ResultSet class)

package com.baidu.emp.jdbcTest;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
import com.mysql.jdbc.Driver;
/**
 *  Start using jdbc Connect to database 
 * @author Admin
 *
 */
public class Test001 {
 
  public static void main(String[] args) throws Exception {
 
    /**
     *  Load Driver 
     */
    //  Method 1 : 
    /*
     * import java.sql.DriverManager; import com.mysql.jdbc.Driver;
     */
    // Driver driver = new Driver();
    // DriverManager.registerDriver(driver);
 
    //  Method 2 : ( Recommended Use )
    Class.forName("com.mysql.jdbc.Driver");
 
    /**
     *  create link 
     */
    String url = "jdbc:mysql://localhost:3306/testjdbc";
    String user = "root";
    String password = "root";
    Connection connection = DriverManager.getConnection(url, user, password);
 
    //  Establish statement object 
    Statement statement = connection.createStatement();
 
    /**
     *  implement SQL , get the result set 
     */
    String sql = "select * from test01";
    ResultSet result = statement.executeQuery(sql);
 
    //  Traversing result set 
    while (result.next()) {
      String name = result.getString("name");
      int id = result.getInt("id");
      System.out.println(name + "\t" + id);
    }
 
    /**
     *  Close links, release resources 
     */
    result.close();
    statement.close();
    connection.close();
  }
}

Prevent SQL injection from switching to prepareStatement

package com.boya.emp.jdbcTest;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
 * SQL Injection, use prepareStatement Object precompilation 
 * @author Admin
 *
 */
public class Test002 {
 
  public static void main(String[] args) throws Exception {
 
    /**
     *  Load Driver 
     */
    Class.forName("com.mysql.jdbc.Driver");
 
    /**
     *  create link 
     */
    String url = "jdbc:mysql://localhost:3306/testjdbc";
    String user = "root";
    String password = "root";
    Connection connection = DriverManager.getConnection(url, user, password);
 
    //  write SQL 
    String sql = "select * from test01 where id = ?";
    // Establish statement Object, precompiled 
    PreparedStatement statement = connection.prepareStatement(sql);
    // Setting parameters 
    statement.setInt(1, 2);
    /**
     *  implement SQL , get the result set 
     */
    ResultSet result = statement.executeQuery();
 
    //  Traversing result set 
    while (result.next()) {
      String name = result.getString("name");
      int id = result.getInt("id");
      System.out.println(name + "\t" + id);
    }
 
    /**
     *  Close links, release resources 
     */
    result.close();
    statement.close();
    connection.close();
  }
}

Code optimization, configuration files, tool classes, add or delete this check

Adding configuration files makes it easy to modify the database and log on to users.

jdbc.properties (Profile Name)

driverName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/testjdbc
userName=root
password=root

Note that no spaces, quotes, etc. are allowed in the middle of writing the configuration file

Tool class: Enhanced code reuse

package com.baidu.emp.utils;
 
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
 
import org.junit.Test;
 
 
 
public class JdbcUtils {
 
  static String driverClassName;
  static String url;
  static String user;
  static String password;
 
  static {
    //  Create Profile Object 
    Properties properties = new Properties();
    //  Load profile input stream 
    InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
    //  Reload Configuration File 
    try {
      properties.load(inputStream);
      //  Get the value of the configuration file 
      driverClassName = properties.getProperty("driverName");
      url = properties.getProperty("url");
      user = properties.getProperty("userName");
      password = properties.getProperty("password");
      Class.forName(driverClassName);
 
    } catch (Exception e) {
      //  throw 
      throw new RuntimeException(e);
    }
  }
 
  /**
   *  Get Connections 
   */
  @Test
  public void testName() throws Exception {
     
    System.out.println(driverClassName);
  }
  public static Connection getConnection() {
    Connection connection = null;
    try {
      connection = DriverManager.getConnection(url, user, password);
    } catch (SQLException e) {
      //  throw 
      throw new RuntimeException(e);
    }
    return connection;
  }
 
  /**
   *  Close links, release resources 
   */
  public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) {
 
    try {
      if (resultSet != null) {
        resultSet.close();
      }
      resultSet = null; //  Clean up garbage in time 
      // Be careful not to end up in an endless loop 
      close(connection, statement);
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
 
  }
 
  /**
   *  Add, delete, release resources 
   */
  public static void close(Connection connection, PreparedStatement statement) {
 
    try {
      if (connection != null) {
        connection.close();
      }
         
      connection = null;
      if (statement != null) {
        statement.close();
      }
      statement = null;
 
    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
 
  }
 
}

Test additions and deletions check:

package com.baidu.emp.jdbcTest;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
 
import com.baidu.emp.utils.JdbcUtils;
 
/**
 *  Use jdbcUtils Connect to database for add-delete check 
 * 
 * @author Admin
 *
 */
public class Test003 {
 
  //  initialize value 
  Connection connection = null;
  PreparedStatement statement = null;
  ResultSet result = null;
 
  @Before
  public void start() throws Exception {
    //  create link 
    connection = JdbcUtils.getConnection();
    System.out.println(" create link ");
  }
 
  @After
  public void end() throws Exception {
    //  Close Link 
    JdbcUtils.close(connection, statement, result);
    System.out.println(" Close Link ");
  }
   
  /**
   * insert data 
   * @throws Exception
   */
  @Test
  public void add() throws Exception {
    String sql = "insert into test01 values(null,?)";
    statement = connection.prepareStatement(sql);
    statement.setString(1, " plum 4");
    int result = statement.executeUpdate();
    if (result!=0) {
      System.out.println(" Added Successfully ");
    }
  }
  /**
   *  Delete data 
   * @throws Exception
   */
  @Test
  public void del() throws Exception {
    String sql = "delete from test01 where id =?";
    statement = connection.prepareStatement(sql);
    statement.setInt(1,3);
    int result = statement.executeUpdate();
    if (result!=0) {
      System.out.println(" Delete succeeded ");
    }
  }
  /**
   *  Modify data 
   * @throws Exception
   */
  @Test
  public void change() throws Exception {
    String sql = "update test01 set name = ? where id = ?";
    statement = connection.prepareStatement(sql);
    statement.setString(1, " Zhang Fei ");
    statement.setInt(2, 2);
    int result = statement.executeUpdate();
    if (result!=0) {
      System.out.println(" Successful modification ");
    }
  }
   
  /**
   *  Query all data 
   * @throws Exception
   */
  @Test
  public void findAll() throws Exception {
    String sql = "select id , name from test01";
    statement = connection.prepareStatement(sql);
    result = statement.executeQuery();
    if (result.next()) {
      System.out.println(" query was successful ");
    }
  }
   
  /**
   *  Conditional Query Data 
   * @throws Exception
   */
  @Test
  public void findOne() throws Exception {
    String sql = "select id , name from test01 where id = ?";
    statement = connection.prepareStatement(sql);
    statement.setInt(1, 2);
    result = statement.executeQuery();
    if (result.next()) {
      System.out.println(" query was successful ");
    }
  }
 
}

These are the relevant knowledge and codes, thank you for your support for this site.