Java JDBC連接數據庫常見操作總結

2024-07-14 08:43:55

字體：大中小

來源：轉載

供稿：網友

本文實例總結了Java JDBC連接數據庫常見操作。分享給大家供大家參考，具體如下：

db.properties配置文件(MySql數據庫)

# db.propertiesjdbc.driver=com.mysql.jdbc.Driverjdbc.url=jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=UTF-8jdbc.username=rootjdbc.password=123456# paramter for BasicDataSourceinitSize=2maxActive=2

db.properties配置文件(Oracle數據庫)

# db.propertiesjdbc.driver=oracle.jdbc.OracleDriverjdbc.url=jdbc:oracle:thin:localhost:1521:orcljdbc.username=rootjdbc.password=123456# paramter for BasicDataSourceinitSize=2maxActive=2

JDBC直接連接數據庫

package JDBC;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Properties;/** * JDBC連接數據庫管理工具類 */public class JDBC {  static String driver;  static String url;  static String username;  static String password;  static {    try {      Properties cfg = new Properties();      InputStream in = JDBC.class.getClassLoader().getResourceAsStream("db.properties");      cfg.load(in);  //將文件內容加載到Properties對象中(以散列表形式存在)      driver = cfg.getProperty("jdbc.driver");      url = cfg.getProperty("jdbc.url");      username = cfg.getProperty("jdbc.username");      password = cfg.getProperty("jdbc.password");      in.close();    } catch (IOException e) {      e.printStackTrace();      throw new RuntimeException(e);    }  }  /**   * 創建數據庫連接   */  public static Connection getConnection() {    try {      Class.forName(driver); //注冊驅動      Connection conn = DriverManager.getConnection(url, username, password);      return conn;    } catch (Exception e) {      e.printStackTrace();      throw new RuntimeException(e);    }  }  /*   * 關閉數據庫的連接   */  public static void close(Connection conn) {    if (conn != null) {      try {        conn.close();      } catch (SQLException e) {        e.printStackTrace();      }    }  }}

通過連接池連接數據庫

package JDBC;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.SQLException;import java.util.Properties;import org.apache.commons.dbcp.BasicDataSource;/** * 連接池版本的數據庫連接管理工具類 */public class DBUtils {  private static String driver;  private static String url;  private static String username;  private static String password;  private static int initSize;  private static int maxActive;  private static BasicDataSource dbs;  static {    dbs = new BasicDataSource();    Properties cfg = new Properties();    try {      InputStream in = DBUtils.class.getClassLoader().getResourceAsStream("db.properties");      cfg.load(in);      // 初始化參數      driver = cfg.getProperty("jdbc.driver");      url = cfg.getProperty("jdbc.url");      username = cfg.getProperty("jdbc.username");      password = cfg.getProperty("jdbc.password");      initSize = Integer.parseInt(cfg.getProperty("initSize"));      maxActive = Integer.parseInt(cfg.getProperty("maxActive"));      in.close();      // 初始化連接池      dbs.setDriverClassName(driver);      dbs.setUrl(url);      dbs.setUsername(username);      dbs.setPassword(password);      dbs.setInitialSize(initSize);      dbs.setMaxActive(maxActive);    } catch (IOException e) {      e.printStackTrace();      throw new RuntimeException(e);    }  }  /**   * 創建數據庫連接,從連接池中獲取連接,如果連接池滿了,則等待.   */  public static Connection getConnection() {    try {      Connection conn = dbs.getConnection();      return conn;    } catch (SQLException e) {      e.printStackTrace();      throw new RuntimeException(e);    }  }  /*   * 關閉數據庫的連接,歸還到連接池   */  public static void close(Connection conn) {    if (conn != null) {      try {        conn.close();      } catch (SQLException e) {        e.printStackTrace();      }    }  }  //回滾，僅在禁用自動提交時使用  public static void rollback(Connection conn) {    if (conn != null) {      try {        conn.rollback();      } catch (SQLException e) {        e.printStackTrace();      }    }  }}

連接數據庫后的使用

Connection conn=null;try{  conn=DbUtils.getConnection();  Statement st=conn.createStatement();  String sql="select id, name from people";  ResultSet rs=st.executeQuery(sql);  while(rs.next()){    int id=rs.getInt("id");    String name=rs.getString("name");    System.out.println(id+","+name);  }  //結果集元數據  ResultSetMetaData meta = rs.getMetaData();  int n = meta.getColumnCount(); //多少列  for(int i=1; i<=n; i++){    String name= meta.getColumnName(i); //獲取列名    System.out.println(name);  }  rs.close();//釋放查詢結果  st.close();//釋放語句對象}catch(Exception e){  e.printStackTrace();}finally {  DbUtils.close(conn);}

預編譯SQL執行及取消自動提交

try {  conn = DBUtils.getConnection();  conn.setAutoCommit(false);  //取消自動提交, 后續手動提交  String sql="update people set name=? where id=? ";  PreparedStatement ps= conn.prepareStatement(sql);  //按照順序發送參數  ps.setString(1, "Lao Wang");  ps.setInt(2, 100);  //執行"執行計劃"  int n=ps.executeUpdate();  conn.commit();  //手動提交} catch (Exception e) {  e.printStackTrace();  DBUtils.rollback(conn); //異?；貪L}finally{  DBUtils.close(conn);}

Statement的addBatch(sql)和executeBatch()方法可以批量執行sql。

Statement st=conn.createStatement();st.addBatch(sql1); //sql1 添加到Statement的緩存中st.addBatch(sql2);st.addBatch(sql3);int[] ary=st.executeBatch();  //執行一批SQL

PreparedStatement也支持批量參數的處理

PreparedStatement ps = conn.prepareStatement(sql);ps.setInt(1, 1);ps.setString(2, "wang");ps.addBatch();   //將參數添加到ps緩存區ps.setInt(1, 2);ps.setString(2, "li");ps.addBatch();   //將參數添加到ps緩存區int[] ary = ps.executeBatch(); // 批量執行

PreparedStatement ps = conn.prepareStatement(sql);

還可以傳入第二個參數用以獲取自增主鍵或者序號自增的列

Java,JDBC,連接數據庫