jdbc连接数据库详解

JDBCjdbc连接数据库：一、直接使用JDBC连接数据库1）所需参数private static String driver;//oracle.jdbc.driver.OracleDriver //com.mysql.jdbc.Driverprivate static String url;//jdbc:mysql://localhost:3306/demo，demo为数据库名  //jdbc:oracle:thin:@localhost:port:demoprivate static String user;private static String pwd;2）流程：1.注册驱动-class.forname(driver)2.创建连接-connection3.获取PreparedStatement并传入sql语句-conn.preparedStatement(sql)//preparedStatement用于处理动态sql，Statement用于处理静态sql，//动态sql中有问号代表占位符4.对占位符进行初始化：ps.setLong(a,long)//第一个参数为第几个问号（从 1 开始）//占位符初始化可以传入多种类型参数ps.setInt(a,int)ps.setString(a,String)……5. 1）如果只是增加，修改，删除等不需要返回结果的操作可以直接执行提交事务ps.executeUpdate();   2）如果是查询，则需要使用ResultSet rs = ps.executeQuery()获取结果集，使用迭代器遍历结果集：ResultSet rs = ps.executeQuery()//结果集while(rs.next()){String str = rs.getString("");int i = rs.getInt("");//括号内为查询的字段名 }6.关闭连接-conn.close()3）案例：1.创建一个工具类DBTool用来获取连接connection,并定义关闭连接方法import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.util.Properties;public class DBTool {private static String driver;private static String url;private static String user;private static String pwd;static {//类加载时只读取一次参数即可Properties p = new Properties();try {p.load(DBTool.class.getClassLoader().getResourceAsStream("db.properties"));driver = p.getProperty("driver");url = p.getProperty("url");user = p.getProperty("user");pwd = p.getProperty("pwd");//只需要注册一次驱动即可Class.forName(driver);} catch (IOException e) {e.printStackTrace();throw new RuntimeException("加载db.properties失败",e);} catch (ClassNotFoundException e) {e.printStackTrace();throw new RuntimeException("找不到驱动类",e);}}/** * 创建连接 * 抛出异常，希望调用者不要忘记catch从而希望他别忘记写finally以及关闭连接。 * @return * @throws SQLException */public Connection getConnection() throws SQLException{return DriverManager.getConnection(url, user, pwd);}/** * 关闭连接 * @param conn */public void close(Connection conn){if(conn != null){try {conn.close();} catch (SQLException e) {e.printStackTrace();throw new RuntimeException("关闭连接失败",e);}}}}2.定义一个DAO类，用来实现连接数据库的具体操作：import java.io.Serializable;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import entity.Dept;import util.DBUtil;public class DeptDao implements Serializable{/** * 版本号 */private static final long serialVersionUID = -3029437005355874537L;/** * 增加 * insert into depts * values * (depts_seq.nextval,?,?) */public void save(Dept dept){Connection conn = null;try {conn = DBUtil.getConnection();String sql = "insert into depts values(depts_seq.nextval,?,?)";PreparedStatement ps = conn.prepareStatement(sql);ps.setString(1, dept.getDname());ps.setString(2, dept.getLoc());ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();throw new RuntimeException("增加部门失败",e);} finally{DBUtil.close(conn);}}/** * 修改 * update depts set dname=?,loc=? where deptno=? *  */public void update(Dept dept){Connection conn = null;try {conn=DBUtil.getConnection();String sql = "update depts set dname=?,loc=? where deptno=?";PreparedStatement ps = conn.prepareStatement(sql);ps.setString(1, dept.getDname());ps.setString(2, dept.getLoc());ps.setInt(3, dept.getDeptno());ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();throw new RuntimeException("修改部门失败",e);} finally{DBUtil.close(conn);}}/** * 删除 * delete from depts where deptno=? */public void delete(Dept dept){Connection conn = null;try {conn = DBUtil.getConnection();String sql = "delete from depts where dname=?";PreparedStatement ps = conn.prepareStatement(sql);ps.setString(1, dept.getDname());ps.executeUpdate();} catch (SQLException e) {e.printStackTrace();throw new RuntimeException("删除部门失败",e);} finally {DBUtil.close(conn);}}/** * 根据id查询 * selecet * from depts where deptno=? */public Dept findById(int id){Connection conn=null;Dept dept = null;try {conn = DBUtil.getConnection();String sql = "select * from depts where deptno=?";PreparedStatement ps = conn.prepareStatement(sql);ps.setInt(1, id);ResultSet rs = ps.executeQuery();while(rs.next()){dept = new Dept();dept.setDeptno(rs.getInt("deptno"));dept.setDname(rs.getString("dname"));dept.setLoc(rs.getString("loc"));return dept;}} catch (SQLException e) {e.printStackTrace();throw new RuntimeException("查询部门失败",e);} finally{DBUtil.close(conn);}return null;}/** * 查询全部部门 */public List<Dept> findAll(){Connection conn=null;try {conn = DBUtil.getConnection();String sql = "select * from depts";PreparedStatement ps = conn.prepareStatement(sql);ResultSet rs = ps.executeQuery();List<Dept> list = new ArrayList<Dept>();while(rs.next()){Dept dept = new Dept();dept.setDeptno(rs.getInt("deptno"));dept.setDname(rs.getString("dname"));dept.setLoc(rs.getString("loc"));list.add(dept);}return list;} catch (SQLException e) {e.printStackTrace();throw new RuntimeException("查询部门失败",e);} finally{DBUtil.close(conn);}}}二、使用连接池连接数据库1）所需参数String driver;String url;String user;String pwd;String initSize；//初始化连接数String maxSize；//最大连接数2)流程1.创建连接池对象- ds = new BasicDataSource2.设置参数ds.setDriverClassName(driver);//加载驱动ds.setUrl(url);//路径ds.setUsername(user);ds.setPassword(pwd);ds.setInitialSize(Integer.valueOf(initSize));ds.setMaxActive(Integer.valueOf(maxSize));3.定义获取连接方法-conn = ds.getConnection()4.同直接使用jdbc连接数据库的3-6条相同.3)案例:import java.io.IOException;import java.sql.Connection;import java.sql.SQLException;import java.util.Properties;import org.apache.commons.dbcp.BasicDataSource;public class DBUtil {//连接池对象private static BasicDataSource ds;static {try {//1.读取参数Properties p = new Properties();p.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));String driver = p.getProperty("driver");String url = p.getProperty("url");String user = p.getProperty("user");String pwd = p.getProperty("pwd");String initSize = p.getProperty("initSize");String maxSize = p.getProperty("maxSize");//2.创建连接池ds = new BasicDataSource();//3.设置参数ds.setDriverClassName(driver);ds.setUrl(url);ds.setUsername(user);ds.setPassword(pwd);ds.setInitialSize(Integer.valueOf(initSize));ds.setMaxActive(Integer.valueOf(maxSize));} catch (IOException e) {e.printStackTrace();throw new RuntimeException("加载db.properties失败",e);}}/** * 获取连接方法 * @return * @throws SQLException  */public static Connection getConnection() throws SQLException{return ds.getConnection();}/** * 该连接由连接池创建，实际上该连接的实现类也是由连接池重写了 * 它所提供的close方法，不再是关闭连接，而是改为归还连接。 * @param conn */public static void close(Connection conn){if(conn != null){try {conn.close();} catch (SQLException e) {e.printStackTrace();throw new RuntimeException("归还关闭失败",e);}}}}总结:不论是使用jdbc直接连接数据库还是使用连接池连接数据库,都是将其封装成工具类,在静态代码块中进行注册驱动,然后定义两个方法,一个获取连接Connection,一个关闭Connection。