1. JDBC
Java Database Connectivity: java 数据库连接
出现JDBC的原因：SUN公司提供一种数据库访问规则，规范，由于数据库的种类比较多，java语言的使用比较广泛，SUN公司提供了一种规范，让其他的数据库提供商去实现底层的访问规则，此时Java程序只需要使用SUN公司提供的JDBC驱动即可
使用JDBC的步骤
1. 注册驱动
// 1. 注册驱动 DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");
2. 建立连接
// 2. 建立连接：参数一：协议+访问的数据库，参数二：用户名，参数三：密码 con=DriverManager.getConnection("jdbc:mysql://localhost/category", "root", "root");
3. 创建statement
// 3. 创建statement,使用数据库，一定要用到这个对象 st = con.createStatement();
4. 执行sql,得到ResultSet
// 4. 执行查询，得到结果集
String sql = "select * from category";
rs = st.executeQuery(sql);
5. 遍历结果集
// 5. 遍历结果集 while (rs.next()) { int id = rs.getInt("cid"); String name = rs.getString("cname"); String cdesc = rs.getString("cdesc"); System.out.println(id + "----------" + name + "--------" + cdesc); }
6. 释放资源
package jdbc_util; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.sql.ResultSet; public class JDBCUtil { public static void release(Connection con, Statement st, ResultSet rs){ closeRs(rs); closeSt(st); closeCon(con); } private static void closeRs(ResultSet rs){ try{ if(rs != null){ rs.close(); } }catch(SQLException e){ e.printStackTrace(); }finally{ rs = null; } } private static void closeSt(Statement st){ try{ if(st != null){ st.close(); } }catch(SQLException e){ e.printStackTrace(); }finally{ st = null; } } private static void closeCon(Connection con){ try{ if(con != null){ con.close(); } }catch(SQLException e){ e.printStackTrace(); }finally{ con = null; } } }
使用properties配置文件
1. 在src文件目录下声明一个文件xxx.properties里面的内容如下
2. 在工具类中，使用静态代码块
static{ try{ // 1. 创建一个属性配置对象 Properties properties = new Properties(); // 2. 使用类加载器，读取src文件下的资源文件 InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"); // 3. 导入输入流 properties.load(is); // 4. 读取属性 driverClass = properties.getProperty("driverClass"); url = properties.getProperty("url"); name = properties.getProperty("name"); password = properties.getProperty("password"); }catch(Exception e){ e.printStackTrace(); } }
数据库的CRUD sql
insert: insert into category(cid, cname, cdesc) values(1, 'wang', 'niubi'); delete: delete from category where cid = 0; query: select * from category; update: update category set age = 30 where id = 1;
使用单元测试，测试代码
1. 定义各个类，Testxxx，里面定义方法：testxxx
2. 添加Junit支持, add library
3. 方法上添加@Test
4. 右键方法名字使用Junit运行
package day_01; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import org.junit.Test; import jdbc_util.JDBCUtil; public class UnitTest { @Test public void testQuery() { Connection con = null; Statement st = null; ResultSet rs = null; String sql = "select * from category"; try { con = JDBCUtil.getCon(); st = con.createStatement(); rs = st.executeQuery(sql); while (rs.next()) { int id = rs.getInt("cid"); String name = rs.getString("cname"); String desc = rs.getString("cdesc"); System.out.println("id:" + id + ",name:" + name + ",desc:" + desc); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } @Test public void testInsert(){ Connection con = null; Statement st = null; ResultSet rs = null; try{ con = JDBCUtil.getCon(); st = con.createStatement(); String sql = "insert into category values(5, '海鲜鱼类', '小龙虾种种美食')"; int result = st.executeUpdate(sql); if(result > 0){ System.out.println("添加成功"); }else{ System.out.println("添加失败"); } }catch(Exception e){ e.printStackTrace(); } } @Test public void testDelete(){ Connection con = null; Statement st = null; ResultSet rs = null; try{ con = JDBCUtil.getCon(); st = con.createStatement(); String sql = "delete from category where cid = 9"; int result = st.executeUpdate(sql); if(result > 0){ System.out.println("删除成功"); }else{ System.out.println("删除失败"); } }catch(Exception e){ e.printStackTrace(); } } @Test public void testUpdate(){ Connection con = null; Statement st = null; ResultSet rs = null; try{ con = JDBCUtil.getCon(); st = con.createStatement(); String sql = "update category set cdesc = '吃货的天下' where cid = 4"; int result = st.executeUpdate(sql); if(result > 0){ System.out.println("更新成功"); }else{ System.out.println("更新失败"); } }catch(Exception e){ e.printStackTrace(); } } }
Dao模式
1. 新建一个dao接口，里面申明数据库访问规则
2. 新建一个dao实现类，具体时间早期定义的函数
3. 具体实现
public interface UserDao { /* * 查询数据库 */ void findAll(); } public class UserDaoImpl implements UserDao{ @Override public void findAll() { Connection con = null; Statement st = null; ResultSet rs = null; try { // 1. 获取连接对象 con = JDBCUtil.getCon(); // 2. 创建statement对象 st = con.createStatement(); String sql = "select * from product"; // 3. 执行查询语句 rs = st.executeQuery(sql); while(rs.next()){ int id = rs.getInt("pid"); String name = rs.getString("pname"); String price = rs.getString("price"); String cno = rs.getString("cno"); System.out.println("id:" + id + ",name:" + name + ",price:" + price + ",cno:" + cno); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ JDBCUtil.release(con, st, rs); } } } public class TestUserDaoImpl { @Test public void testFindAll(){ UserDao dao = new UserDaoImpl(); dao.findAll(); } }
##Statement的安全问题
1. Statement执行，实际上是拼接sql语句，先拼接sql语句，然后一起执行
2. PrepareStatement用来替换Statement对象
public interface UserDao { /* * 插入数据 */ void insert(String username, String password); /* * 查询数据库 */ void findAll(); /* * 登陆数据库 */ void login(String username, String password); /* * 删除数据 */ void delete(int id); /* * 更新数据 */ void update(int id, String name); } public class UserDaoImpl implements UserDao{ @Override public void findAll() { Connection con = null; Statement st = null; ResultSet rs = null; try { // 1. 获取连接对象 con = JDBCUtil.getCon(); // 2. 创建statement对象 st = con.createStatement(); String sql = "select * from student"; // 3. 执行查询语句 rs = st.executeQuery(sql); while(rs.next()){ int id = rs.getInt("id"); String username = rs.getString("username"); String password = rs.getString("password"); System.out.println("id:" + id + ",username:" + username + ",password:" + password); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ JDBCUtil.release(con, st, rs); } } @Override public void login(String username, String password) { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { con = JDBCUtil.getCon(); String sql = "select * from student where username = ? and password = ?"; // 将先对sql语句做语法的校验，?中的内容，不管传递什么，都把它看成是字符串 ps = con.prepareStatement(sql); // ?对应的索引从1开始 ps.setString(1, username); ps.setString(2, password); rs = ps.executeQuery(); if(rs.next()){ System.out.println("登陆成功"); }else{ System.out.println("登陆失败"); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ JDBCUtil.release(con, ps, rs); } } @Override public void insert(String username, String password) { PreparedStatement ps = null; Connection con = null; try { con = JDBCUtil.getCon(); String sql = "insert into student values(?, ?, null)"; ps = con.prepareStatement(sql); ps.setString(1, username); ps.setString(2, password); int result = ps.executeUpdate(); if(result > 0){ System.out.println("插入成功"); }else{ System.out.println("插入失败"); } } catch (Exception e) { e.printStackTrace(); } finally{ JDBCUtil.release(con, ps); } } @Override public void delete(int id) { PreparedStatement ps = null; Connection con = null; try { con = JDBCUtil.getCon(); String sql = "delete from student where id = ? "; ps = con.prepareStatement(sql); ps.setInt(1, id); int result = ps.executeUpdate(); if(result > 0){ System.out.println("删除成功"); }else{ System.out.println("删除失败"); } } catch (Exception e) { e.printStackTrace(); } finally{ JDBCUtil.release(con, ps); } } @Override public void update(int id, String name) { Connection con = null; PreparedStatement ps = null; try{ con = JDBCUtil.getCon(); String sql = "update student set username = ? where id = ?"; ps = con.prepareStatement(sql); ps.setString(1, name); ps.setInt(2, id); int result = ps.executeUpdate(); if(result > 0){ System.out.println("更新成功"); }else{ System.out.println("更新失败"); } } catch(Exception e){ e.printStackTrace(); } finally{ JDBCUtil.release(con, ps); } } } public class TestUserDaoImpl { @Test public void testFindAll(){ UserDao dao = new UserDaoImpl(); dao.findAll(); } @Test public void testLogin(){ UserDao dao = new UserDaoImpl(); dao.login("wangjian", "2015141452153"); } @Test public void testInsert(){ UserDao dao = new UserDaoImpl(); dao.insert("wjx", "2015141452155"); } @Test public void testDelete(){ UserDao dao = new UserDaoImpl(); dao.delete(5); } @Test public void testUpdate(){ UserDao dao = new UserDaoImpl(); dao.update(5, "wangjingxuan"); } } public class JDBCUtil { static String driverClass = null; static String url = null; static String name = null; static String password = null; static{ try{ // 1. 创建一个属性配置对象 Properties properties = new Properties(); // 2. 使用类加载器，读取src文件下的资源文件 InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"); // 3. 导入输入流 properties.load(is); // 4. 读取属性 driverClass = properties.getProperty("driverClass"); url = properties.getProperty("url"); name = properties.getProperty("name"); password = properties.getProperty("password"); }catch(Exception e){ e.printStackTrace(); } } public static Connection getCon(){ Connection con = null; try { Class.forName(driverClass); // 可以不写 con = DriverManager.getConnection(url, name, password); } catch (Exception e) { e.printStackTrace(); } return con; } public static void release(Connection con, Statement st, ResultSet rs){ closeRs(rs); closeSt(st); closeCon(con); } public static void release(Connection con, Statement st){ closeCon(con); closeSt(st); } private static void closeRs(ResultSet rs){ try{ if(rs != null){ rs.close(); } }catch(SQLException e){ e.printStackTrace(); }finally{ rs = null; } } private static void closeSt(Statement st){ try{ if(st != null){ st.close(); } }catch(SQLException e){ e.printStackTrace(); }finally{ st = null; } } private static void closeCon(Connection con){ try{ if(con != null){ con.close(); } }catch(SQLException e){ e.printStackTrace(); }finally{ con = null; } } }