JDBC的简易封装及优化

JDBC原代码

package com.openlab.web;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//1.使用DriverManager 进行加载驱动 Driver
//2.通过驱动的加载拿到连接对象 Connection
//3.通过connection对象获取到 Statement
//4.使用Statement对象 进行sql查询或更新
 
 
public class test {
	public static void main(String[] args)throws ClassNotFoundException,SQLException{
                //加载驱动
		Class.forName("com.mysql.jdbc.Driver");
		//拿到连接对象
		Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
                 //获取到statement   
		Statement st=conn.createStatement();
                //进行查询，返回结果集
		ResultSet rs= st.executeQuery("select * from tb1");
                //对结果进行遍历
		while(rs.next()) {
			System.out.println(rs.getString("uername")+":"+rs.getInt("age")+":"+rs.getFloat("salary"));
			
		}
		
		rs.close();
		st.close();
		conn.close();
	}
 
}

简易封装及优化后的代码

package com.openlab.utils;


	//优化：
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.List;
	 
	public class JDBCUtils {
		
		private String driver ="com.mysql.jdbc.Driver";
		private static String url = "jdbc:mysql://localhost:3306/demo";
		private static String username = "root";
		private static String password = "123456";
		private Connection conn = null;
		private Statement st = null;
		private ResultSet rs = null;
		
		
		//因为加载驱动这件事情 在程序启动的时候就被加载且只执行一次
		static{
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		/**
		 * 获取连接对象
		 * @return
		 * @throws SQLException
		 */
		public static Connection getConnection() throws SQLException{
			return DriverManager.getConnection(url, username,password);
		}
		
		
		/**
		 * 添加，修改，删除
		 */
		
		public void update(String sql){
			
			try {
				conn = getConnection();
				
				st = conn.createStatement();
				
				int bRet = st.executeUpdate(sql);
				
				System.out.println(bRet);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				jdbcClose(conn, st);
			}
		}
		
		/**
		 * 多条记录的查询  List  ArrayList
		 */
		
		public List queryByAll(String sql){
			
			List list = null;
			try {
				conn = getConnection();
				st = conn.createStatement();
				ResultSet rs = st.executeQuery(sql);
				list = rsToList(rs);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				jdbcClose(conn, st);
			}
			
			return list;
		}
		
		
		
		
		/**
		 * 单条记录的查询 Object
		 */
		
		public Object queryByOne(String sql){
			
			Object obj = null;
			try {
				conn = getConnection();
				st = conn.createStatement();
				ResultSet rs = st.executeQuery(sql);
				obj = rsToObj(rs);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				jdbcClose(conn, st);
			}
			return obj;
		}
		
		
		
		/**
		 * 释放资源
		 * mysql的连接资源 比较珍稀的资源
		 * 尽晚的获取连接，尽早释放资源
		 * @param conn
		 * @param st
		 * @param rs
		 */
		public static void jdbcClose(Connection conn,Statement st,ResultSet rs){
			
			try {
				rs.close();
				if(rs != null){
					rs = null;
			    }
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				
				try {
					st.close();
					if(st!=null){
						st = null;
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally{
					try {
					conn.close();
					if(conn!=null){
						conn = null;
					}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
		}
		
		public static void jdbcClose(Connection conn,Statement st){
			
			try {
				st.close();
				if(st!=null){
					st = null;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				try {
				conn.close();
				if(conn!=null){
					conn = null;
				}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		
		
		public Object rsToObj(ResultSet rs) {
			return null;
		}
		public List rsToList(ResultSet rs) {
			return null;
		}
	
}