Day03--JDBC
怎么说呢，本来挺有激情的，突然炸掉了，不知道今天的效率会咋样A
使用Java代码发送sql语句的技术
1、JDBC接口核心的API：java.sql 和 javax.sql
1.1、Driver接口：Java驱动类接口
1.2、DriverManager类：驱动管理器类
1.3、Connection接口：表示Java程序和数据库连接对象
1.4、statement接口：用于执行静态的sql语句
1.5、preparedstatement接口：用于执行预编译sql语句
1.6、resultset接口：用于封装查询出来的数据
1.7、CallableStatement：程序中调用存储过程
/** * Jdbc工具类 * */ public class JdbcUtil { private static String url = null; private static String user = null; private static String password = null; private static String driverClass = null; /** * 静态代码块（只加载一次） * */ static{ //读取db.properties文件 Properties properties = new Properties(); //.java命令运行的目录 //FileInputStream in = new FileInputStream("./src/db.properties"); //使用类路径的读取方式,在Java项目中，classpath的根目录从bin目录开始；web中，从WEB-INF/classes目录开始 InputStream in = JdbcUtil.class.getResourceAsStream("/db.properties"); try { //加载文件 properties.load(in); //读取信息 url = properties.getProperty("url"); user = properties.getProperty("user"); password = properties.getProperty("password"); driverClass = properties.getProperty("driverClass"); //注册驱动程序 Class.forName(driverClass); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection(){ try { Connection connection = DriverManager.getConnection(url,user,password); return connection; } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(); } } public static void close(Connection conn,Statement stmt,ResultSet rs){ if(rs!=null) try { rs.close(); } catch (SQLException e1) { e1.printStackTrace(); throw new RuntimeException(e1); } if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); throw new RuntimeException(e); } } } } public void test1(){ Connection connection = null; PreparedStatement preparedStatement = null; try { connection = JdbcUtil.getConnection(); String sql = "update manager set account = ? where id = ?"; preparedStatement = (PreparedStatement) connection.prepareStatement(sql); preparedStatement.setString(1, "Rose黄"); preparedStatement.setInt(2, 2); int count = preparedStatement.executeUpdate(); System.out.println("受影响的行数"+count); } catch (Exception e) { e.printStackTrace(); }finally { JdbcUtil.close(connection, preparedStatement, null); } } public void test2(){ Connection connection = null; Statement statement = null; ResultSet rs = null; try { connection = JdbcUtil.getConnection(); statement = connection.createStatement(); String sql = "select * from manager"; rs = statement.executeQuery(sql); while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("account"); System.out.println(id+";"+name); } } catch (Exception e) { e.printStackTrace(); }finally { JdbcUtil.close(connection, statement, rs); } }
2、批处理：一次批量执行大量的命令
void addBatch(String sql)：添加批处理
void clearBatch()：清空批处理
int[] executeBatch()：执行批处理
private Connection conn; private PreparedStatement ps; private ResultSet rs; public void save(List<Admin> list){ String sql = "insert into admin (username,pwd) values (?,?)"; try { conn = JdbcUtil.getConnection(); ps = conn.prepareStatement(sql); for (int i = 0; i < list.size(); i++) { Admin admin = list.get(i); ps.setString(1, admin.getUsername()); ps.setString(2, admin.getPwd()); //添加批处理 ps.addBatch(); }
ps.executeBatch(); //执行批处理
ps.clearBatch(); //清除批处理 } catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtil.close(conn, ps, rs); } } public static void main(String[] args) throws Exception { List<Admin> list = new ArrayList<Admin>(); for (int i = 0; i < 5; i++) { Admin admin = new Admin(); admin.setUsername("xiao"); admin.setPwd("12"); list.add(admin); } AdminDao dao = new AdminDao(); dao.save(list); }
3、插入数据，获取自增长值
参照员工、部门表
private Connection conn; private java.sql.PreparedStatement ps; private ResultSet rs; public void save(Employee emp){ String sql_dept = "insert into dept (name) values (?)"; String sql_emp = "insert into employee (name,deptid) values (?,?)"; int deptid = 0; try { conn = JdbcUtil.getConnection(); //保存部门，获取自增长：指定返回自增长标记 ps = conn.prepareStatement(sql_dept, Statement.RETURN_GENERATED_KEYS); ps.setString(1, emp.getDept().getName()); ps.executeUpdate(); //获取保存部门自增长的主键 rs = ps.getGeneratedKeys(); if(rs.next()) deptid = rs.getInt(1); ps = conn.prepareStatement(sql_emp); ps.setString(1, emp.getName()); ps.setInt(2,deptid); ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); }finally { JdbcUtil.close(conn, ps, rs); } } public static void main(String[] args) { Dept dept = new Dept(); dept.setName("市场部"); Employee employee = new Employee(); employee.setName("柳柳"); employee.setDept(dept); Dao dao = new Dao(); dao.save(employee); }
4、事务
一组最小逻辑操作单元，里面有多个操作组成。组成事务的每一部分要同时提交成功，如果一个操作失败，则整个操作回滚。通俗的讲，类似于银行转账系统，到了转账的最后一步，但是转账失败，则前面的操作全部返回，需要再次从头开始
原子性：要么都发生，要么都不发生；
一致性：事务过程中，数据处于一致状态
隔离性：多个并发事务之间要相互隔离
持久性：事务一旦提交成功，对数据库中数据的改变是持久性的
参照账户表
private Connection connection; private java.sql.PreparedStatement ps; //转账，没有使用事务 public void trans1(){ String sql_z = "update account set money = money + 1000 where name = '张三'"; String sql_l = "update account set money = money - 1000 where name = '李四'"; try { connection = JdbcUtil.getConnection(); //默认开启隐士事务 connection.setAutoCommit(true); ps = connection.prepareStatement(sql_l); ps.executeUpdate(); ps = connection.prepareStatement(sql_z); ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); }finally { JdbcUtil.close(connection, ps, null); } } //转账，使用事务 public void trans2(){ String sql_z = "update account set money = money + 1000 where name = '张三'"; String sql_l = "update account set money = money - 1000 where name = '李四'"; try { connection = JdbcUtil.getConnection(); //设置事务为手动提交 connection.setAutoCommit(false); ps = connection.prepareStatement(sql_z); ps.executeUpdate(); ps = connection.prepareStatement(sql_l); ps.executeUpdate(); //设置事务回滚位置 //Savepoint sp = connection.setSavepoint(); } catch (Exception e) { try { //出现异常，回滚事务 connection.rollback(); //connection.rollback(savepoint); 回滚到指定代码 } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); }finally { try { //所有操作执行成功，提交事务 connection.commit(); JdbcUtil.close(connection, ps, null); } catch (SQLException e) { e.printStackTrace(); } } }
5、jdbc中大文本类型的处理（涉及到流的问题，后面统一处理）
Oracle ：Clob 长文本类型；Bolb 二进制类型
MySQL：Text 长文本类型；Blob 二进制类型
6、jdbc的优化
6.1、BeanUtils组件
6.2、元数据
6.3、BaseDao
6.4、DBUtils组件
