Java JDBC连接数据库常见操作总结

# 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.propertiesjdbc.driver=oracle.jdbc.OracleDriverjdbc.url=jdbc:oracle:thin:localhost:1521:orcljdbc.username=rootjdbc.password=123456# paramter for BasicDataSourceinitSize=2maxActive=2

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);}

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); //异常回滚}finally{  DBUtils.close(conn);}

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

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(); // 批量执行