JDBC学习
JAVA Database Connectivity java 数据库连接
- 为什么会出现JDBC
SUN公司提供的一种数据库访问规则、规范, 由于数据库种类较多，并且java语言使用比较广泛，sun公司就提供了一种规范，让其他的数据库提供商去实现底层的访问规则。 我们的java程序只要使用sun公司提供的jdbc驱动即可。
JDBC的步骤
-
load the driver
此时的意思是向DriverManager注册驱动
try { Class.forName(driverClass); } catch(ClassNotFoundException e) { e.printStackTrace(); }forName() throws ClassNotFoundException
driverClass为相应的驱动类 eg:Oracle.jdbc.driver.OracleDriver
-
connect to database
try { conn = DriverManager.getConnection(url,user,password); } catch(SQLException e) { e.printStackTrace(); }getConnection()方法抛出SQLException
-
execute sql statement
Statement stmt = null; stmt = conn.createStatement(); String sql = "select * from dept"; rs = stmt.executeQuery(sql); -
retrieve the data
int deptno = 0; String dname = null; String loc = null; while(rs.next()) { deptno = rs.getInt("deptno"); dname = rs.getString("dname"); loc = rs.getString("loc"); System.out.println("deptno = " + deptno +" dname = " + dname + " loc = " + loc); } -
close the resource
rs.close(); stmt.close(); conn.close(); -
程序整体
public static void main(String[] args) { Connection connection = null; Statement statement = null; ResultSet result = null; try { //注册驱动 Class.forName("com.mysql.jdbc.Driver"); //建立连接 connection = DriverManager.getConnection("jdbc:mysql://localhost/student", "root", "root"); //创建语句 statement = connection.createStatement(); //执行查询，得到结果集 String sql = "select * from t_stu"; result = statement.executeQuery(sql); //遍历结果集 while(result.next()) { int id = result.getInt("id"); String name = result.getString("name"); int age = result.getInt("age"); System.out.println("id = " + id + " name = " + name + " age = " + age); } }catch(ClassNotFoundException e) { e.printStackTrace(); } catch(SQLException e) { e.printStackTrace(); } finally { JDBCUtil.closeResource(connection, statement, result); } }
JDBC工具类的创建
因为有关JDBC的操作中经常性的会用到以上步骤，所以最好是写入包装类，等到使用时直接添加即可
工具类名称:JDBCUtil
-
配置文件的生成--new file--jdbc.properties
需要的类有：java.util.Properities
driverClass = com.mysql.jdbc.Driver url = jdbc:mysql://localhost/student user = root password = root -
配置文件的使用
static中使用
static {
//创建属性配置对象
Properties properties = new Properties();//使用类加载器，读取src底下的资源文件 InputStream iStream = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"); //导入输入流: try { properties.load(iStream); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } //读取属性 driverClass = properties.getProperty("driverClass"); url = properties.getProperty(url); name = properties.getProperty(name); password = properties.getProperty(password);}
将 load the driver 和 Connect to database 合并为getConne()方法
public static Connection getConn() {
Connection conn = null;
//--load the driver
try {
Class.forName(driverClass);
}
catch(ClassNotFoundException e) {
e.printStackTrace();
}
//--connect to database;
try {
conn = DriverManager.getConnection(url,user,password);
}
catch(SQLException e) {
e.printStackTrace();
}
return conn;
}
-
添加closeResource()方法
主程序中finally中之执行closeResource()方法，进行资源释放
public static void closeResource(ResultSet rs,Statement stmt,Connection conn){ closeRs(rs); closeStmt(stmt); closeConn(conn); } public static void closeResource(PreparedStatement pstmt,Connection conn){ closeStmt(pstmt); //preparedStatement是statement的一个子类 closeConn(conn);}
static void closeRs(ResultSet rs) { try { if(rs != null) rs.close(); } catch(SQLException e) { e.printStackTrace(); } finally { rs = null; } } static void closeStmt(Statement stmt) { try { if(stmt != null) stmt.close(); } catch(SQLException e) { e.printStackTrace(); } finally { stmt = null; } } static void closeConn(Connection conn) { try { if(conn != null) conn.close(); } catch(SQLException e) { e.printStackTrace(); } finally { conn = null; } }此时注意方法的重载
JDBC的DML使用
- 预备知识
JUnit的使用：
①添加Junit的lib
②new Junit Test Case
-
testQuery()
--测试查询public void testQuery() { Connection conn = JDBCUtil.getConn(); Statement stmt = null; ResultSet rs = null; try { //execute sql statement stmt = conn.createStatement(); String sql = "select * from dept"; rs = stmt.executeQuery(sql); /* 注意:executeQuery()返回的是ResultSet */ //retrieve the data int deptno = 0; String dname = null; String loc = null; while(rs.next()) { deptno = rs.getInt("deptno"); dname = rs.getString("dname"); loc = rs.getString("loc"); System.out.println("deptno = " + deptno +" dname = " + dname + " loc = " + loc); } } catch (SQLException e) { e.printStackTrace(); } finally { //close the resource JDBCUtil.closeResource(rs, stmt, conn); } } -
testInsert()
public void testInsert() { Connection conn = JDBCUtil.getConn(); Statement stmt = null; try { //execute sql statement stmt = conn.createStatement(); String sql = "insert into dept values(50,'What','Shanxi')"; int result = stmt.executeUpdate(sql); if(result == 0) { System.out.println("添加失败"); } else { System.out.println("添加成功"); } }catch(SQLException e) { e.printStackTrace(); }finally { JDBCUtil.closeResource(stmt, conn); } } -
testInsert_2()--使用preparedStatement
public void testInsert_2() { Connection conn = JDBCUtil.getConn(); PreparedStatement prestmt = null; try { //execute sql statement prestmt = conn.prepareStatement("insert into dept values(?,?,?)"); prestmt.setInt(1, 55); prestmt.setString(2,"hahahaha"); prestmt.setString(3, "henan"); int result = prestmt.executeUpdate(); if(result == 0) { System.out.println("添加失败"); } else { System.out.println("添加成功"); } }catch(SQLException e) { e.printStackTrace(); }finally { JDBCUtil.closeResource(prestmt, conn); //此时运用到多态 } } -
testDelete()
public void testDelete() { Connection conn = JDBCUtil.getConn(); Statement stmt = null; try { //execute sql statement stmt = conn.createStatement(); String sql = "delete from dept where deptno = 55"; int result = stmt.executeUpdate(sql); if(result == 0) { System.out.println("删除失败"); } else { System.out.println("删除成功"); } }catch(SQLException e) { e.printStackTrace(); }finally { JDBCUtil.closeResource(stmt, conn); } } -
testUpdate()
@Test public void testUpdate() { Connection conn = JDBCUtil.getConn(); Statement stmt = null; try { //execute sql statement stmt = conn.createStatement(); String sql = "update dept set dname = 'xixixixi' where deptno = 55"; int result = stmt.executeUpdate(sql); if(result == 0) { System.out.println("更新失败"); } else { System.out.println("更新成功"); } }catch(SQLException e) { e.printStackTrace(); }finally { JDBCUtil.closeResource(stmt, conn); } }
DAO模式
Data Access Object 访问数据对象
可以实现业务逻辑与数据库访问相分离
一般的DAO的封装由以下另个原则：
一个表对应一个dao接口，对应一个相应实现类。
对于DAO接口，必须由具体的类型定义。这样可以避免被错误地调用
一般步骤
-
创建相关表的接口--new Interface--DeptDao--此时例子是对dept表进行操作
里面封装相应对于此表的操作：eg: query,delete,update,insert
package com.anna.dao; public interface DeptDao { //查询所有 public void findAll(); } -
创建接口的实现类--一般与接口在不同包中
----继承接口
----实现接口中的抽象方法
public class DeptDaoImpl implements DeptDao{ public void findAll() { //Connect to database Connection conn = JDBCUtil.getConn(); //execute sql statement Statement stmt= null; ResultSet rs = null; try { stmt = conn.createStatement(); String sql = "select * from dept"; rs = stmt.executeQuery(sql); while(rs.next()) { int deptno = rs.getInt("deptno"); String dname = rs.getString("dname"); String loc = rs.getString("loc"); System.out.println("deptno = " + deptno + " dname = " + dname + " loc = " + loc + "\n"); } } catch(SQLException e) { e.printStackTrace(); }finally { JDBCUtil.closeResource(stmt,conn); } } } -
利用JUnit测试相关方法
public class TestDeptDaoImpl { @Test public void testFindAll() { DeptDao dd = new DeptDaoImpl(); //接口引用指向实现类 dd.findAll(); } }
Statement的安全问题
-
Statement执行 ，拼接sql语句的。 先拼接sql语句，然后在一起执行。
---此时可能会导致错误
PreparedStatement的学习
由于statement本身存在的问题，引出了PreparedStatement
//execute sql statement
PreparedStatement pstmt= null;
ResultSet rs = null;
try {
String sql = "select * from dept where deptno = ? and dname = ? and loc = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, deptno);
pstmt.setString(2,dname);
pstmt.setString(3,loc);
rs = pstmt.executeQuery();
if(rs.next()) {
System.out.println("验证成功");
}
else {
System.out.println("验证失败");
}
}
catch(SQLException e) {
e.printStackTrace();
}finally {
JDBCUtil.closeResource(pstmt,conn);
}
总结
JDBC的学习
JDBC工具类的创建
JDBC的DML的实现
DAO模式的了解
PreparedStatement的学习
