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

# db.properties

jdbc.driver=com.mysql.jdbc.driver

jdbc.url=jdbc:mysql: //localhost:3306/demo?useunicode=true&characterencoding=utf-8

jdbc.username=root

jdbc.password= 123456

# paramter for basicdatasource

initsize= 2

maxactive= 2

# db.properties

jdbc.driver=oracle.jdbc.oracledriver

jdbc.url=jdbc:oracle:thin:localhost: 1521 :orcl

jdbc.username=root

jdbc.password= 123456

# paramter for basicdatasource

initsize= 2

maxactive= 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测试数据mons.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测试数据mit();  //手动提交

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