JDBC进阶

写在之前

在我们了解一些用java连接数据库的详细过程后，发现实现增删改查无疑是几个步骤（加载驱动、建立连接、定义sql语句、执行sql语句、得到结果），所以我们可以用一个类来实现，在使用时调用即可。

具体过程

定义

以连接oracle数据库为例，定义这些固定不变的量

	 public static final String driver ="oracle.jdbc.driver.OracleDriver";
	 public static final String url="jdbc:oracle:thin:@localhost:1521:orcl";
	 public static final String user="scott";
	 public static final String password="root";

获取连接

加载驱动，得到连接

	 public static Connection getConnection() throws ClassNotFoundException, SQLException {
		 Connection conn=null;
		 Class.forName(driver);
		 conn=DriverManager.getConnection(url,user,password);
		 return conn;
	 }

执行sql语句（增删改）

传入参数 sql语句以及集合（把sql语句中 ？的值存入集合中）

1.赋值

	 public static void setList(PreparedStatement pstmt,List list) throws Exception {
		 for(int i =0;i<list.size();i++) {
			 Object v =list.get(i);
			 pstmt.setObject(i+1, v);
		 } 
	 }

2.执行sql语句

 /**
	  * @param String sql List list
	  * @return int（更新记录的个数）
	  * */
	 public static int executeUpdate(String sql,List list) throws Exception {
		 Connection conn=null;
		 PreparedStatement pstmt=null;
		 conn=getConnection();
		 pstmt=conn.prepareStatement(sql);
		 setList(pstmt, list);
		 int i=pstmt.executeUpdate();
		 closeAll(null, pstmt, conn);
		 return i;
	 }

3.关闭连接

public static void closeAll(ResultSet rs,Statement stmt,Connection conn){
			try {
				if(rs!=null){rs.close();}
				if(stmt!=null){stmt.close();}
				if(conn!=null){conn.close();}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

执行sql语句（查）

传入参数 sql语句以及集合（把sql语句中 ？的值存入集合中）

1.执行sql语句

  /**
	  * @param sql list
	  * @return ResultSet rs
	  * */
	 public static ResultSet executeQuery(String sql,List list) throws Exception {
		 Connection conn=null;
		 PreparedStatement pstmt =null;
		 ResultSet rs =null;
		 conn =getConnection();
		 pstmt =conn.prepareStatement(sql);
		 setList(pstmt, list);
		 rs=pstmt.executeQuery();
		 close(rs);
		 return rs;
	 }

3.关闭连接

	public static void close(ResultSet rs) {
			try {
		closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

完整代码

package r2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

public class baseDB {
	 public static final String driver ="oracle.jdbc.driver.OracleDriver";
	 public static final String url="jdbc:oracle:thin:@localhost:1521:orcl";
	 public static final String user="scott";
	 public static final String password="root";
	
	 /**
	  * 	获取连接
	  * @return conn
	  * 
	  * */
	 public static Connection getConnection() throws ClassNotFoundException, SQLException {
		 Connection conn=null;
		 Class.forName(driver);
		 conn=DriverManager.getConnection(url,user,password);
		 return conn;
	 }
	 
	 /**
	  * @param sql list
	  * @return int（更新记录的个数）
	  * */
	 public static int executeUpdate(String sql,List list) throws Exception {
		 Connection conn=null;
		 PreparedStatement pstmt=null;
		 conn=getConnection();
		 pstmt=conn.prepareStatement(sql);
		 setList(pstmt, list);
		 int i=pstmt.executeUpdate();
		 closeAll(null, pstmt, conn);
		 return i;
  
	 }
	 /**
	  * @param sql list
	  * @return ResultSet rs
	  * */
	 public static ResultSet executeQuery(String sql,List list) throws Exception {
		 Connection conn=null;
		 PreparedStatement pstmt =null;
		 ResultSet rs =null;
		 conn =getConnection();
		 pstmt =conn.prepareStatement(sql);
		 setList(pstmt, list);
		 rs=pstmt.executeQuery();
		
		 return rs;
	 }
	 
	 
	 public static void setList(PreparedStatement pstmt,List list) throws Exception {
		 for(int i =0;i<list.size();i++) {
			 Object v =list.get(i);
			 pstmt.setObject(i+1, v);
		 } 
	 }
		public static void closeAll(ResultSet rs,Statement stmt,Connection conn){
			try {
				if(rs!=null){
					rs.close();
				}
				if(stmt!=null){
					stmt.close();
				}
				if(conn!=null){
					conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		public static void close(ResultSet rs) {
			try {
				closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
}

举例（向数据库中添加一条记录）

继承父类，调用其方法，传入sql语句和集合即可。增删改类似。

package r2;

import java.util.ArrayList;
import java.util.List;

public class insertDemo extends baseDB {
		public static void main(String[] args) {
			List list =new ArrayList();
			String sql="insert into dept(deptno,dname,loc) values(?,?,?)";
			list.add(0, 70);
			list.add(1,"wuhan");
			list.add(2,"wuhan");
			try {
				executeUpdate(sql, list);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
}

举例（查询）

package r2;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class selectDemo extends baseDB{
	public static void main(String[] args) {
		List list =new ArrayList();
		String sql ="select * from dept";
		try {
			ResultSet rs=executeQuery(sql, list);
			while(rs.next()) {
				System.out.println(rs.getObject(1)+"  "+rs.getObject(2)+" "+rs.getObject(3));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
}