jdbc:java操纵数据库技术
在jdk中 : 定义了多个接口,接口中约定了一些规则(抽象方法)
数据库厂商 : 定义一些类,实现接口,并且按约定实现接口中的方法
java.sql.Connection : 规定数据库的连接
java.sql.PreparedStatement:sql发送工具
java.sql.ResultSet: 处理结果集
java.sql.Driver: 数据库驱动
java.sql.DriverManager: 数据库驱动管理
JDBC主要步骤
PLSql操纵数据库的步骤
1.连接数据库 用户名 密码 数据库库名(xe)
2.创建sql发送窗口
3.编写sql.发送sql
4.接收并处理结果集
5.关闭客户端
JDBC
1.加载驱动
2.获得数据库连接对象(Connection对象)
3.获取sql发送工具对象(PreparedStatement对象)
4.编写sql,发送sql
5.接收并处理结果集对象(ResultSet对象)
6.关闭资源 ResultSet PreparedStatement Connection
第一个JDBC代码
需求:向t_person表中添加一条数据
create table t_person( id number(3) primary key, name varchar2(50), age number(3), sex number(1), ---0表示女性 1表示男性 mobile varchar2(20), address varchar2(100) ) insert into t_person values(100,'王xx',18,0,'12312341234','河南郑州')
导包: ojdbc6.jar 1.项目---右键----new---folder---lib---将ojdbc6.jar放入lib 2.jar包---右键---build path-----add to build path class JDBC{ public static void main(String[] args) throws Exception { //1.加载驱动 Class.forName("oracle.jdbc.OracleDriver"); //2.获得数据库连接对象(Connection对象) /** * url:指明数据库的路径 * jdbc:oracle:thin:@电脑ip:端口号:数据库库名 * jdbc:oracle:thin:@localhost:1521:xe */ Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr"); System.out.println(conn); //3.获取sql发送工具对象(PreparedStatement对象) String sql = "insert into t_person values(111,'小杰',16,0,'11122222222','河南郑州')";//sql不加; PreparedStatement pstm = conn.prepareStatement(sql); //4.发送sql pstm.executeUpdate() 作用于dml(增删改)语句 返回值这次sql操作影响了表中的几条数据 int executeUpdate = pstm.executeUpdate(); System.out.println("影响表中数据:"+executeUpdate); //5.接收并处理结果集对象(ResultSet对象) //6.关闭资源 ResultSet PreparedStatement Connection 后开的先关 pstm.close(); conn.close(); } }
JDBC的查询
ResultSet
结果集对象
需求：查询id=100的信息
public static void main(String[] args) throws Exception { //加载驱动 Class.forName("oracle.jdbc.OracleDriver"); //获取数据库连接对象 Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","hr","hr"); //获取sql发送工具对象 PreparedStatement pstm = conn.prepareStatement("select * from t_person "); //发送sql ResultSet rs = pstm.executeQuery(); //接收并处理结果集 //将指针下移一行航 while(rs.next()) { //代表有值 int id = rs.getInt("id"); String name = rs.getString("name"); int age = rs.getInt("age"); int sex = rs.getInt(4); String mobile = rs.getString(5); String addr = rs.getString(6); System.out.println("id="+id+" name="+name+" age="+age+" sex="+sex+" mobile="+mobile+" addr="+addr); } //关闭资源 rs.close(); pstm.close(); conn.close(); }
1.拼接字符串
问题:不能防止sql注入
2.设置占位符
占位符：？
sql当中需要动态写入值的地方
public static void main(String[] args) throws Exception { Scanner sc = new Scanner(System.in); System.out.println("id:"); int id = sc.nextInt(); System.out.println("name:"); String name = sc.next(); System.out.println("age:"); int age = sc.nextInt(); System.out.println("sex:"); int sex = sc.nextInt(); System.out.println("mobile:"); String mobile = sc.next(); System.out.println("address:"); String addr = sc.next(); //1.加载驱动 Class.forName("oracle.jdbc.OracleDriver"); //2.获得数据库连接对象(Connection对象) /** * url:指明数据库的路径 * jdbc:oracle:thin:@电脑ip:端口号:数据库库名 * jdbc:oracle:thin:@localhost:1521:xe */ Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr"); System.out.println(conn); //3.获取sql发送工具对象(PreparedStatement对象) String sql = "insert into t_person values(?,?,?,?,?,?)";//sql不加; PreparedStatement pstm = conn.prepareStatement(sql); //给站位符赋值 pstm.setInt(1, id); pstm.setString(2, name); pstm.setInt(3, age); pstm.setInt(4, sex); pstm.setString(5, mobile); pstm.setString(6, addr); //4.发送sql pstm.executeUpdate() 作用于dml(增删改)语句 返回值这次sql操作影响了表中的几条数据 int executeUpdate = pstm.executeUpdate(); System.out.println("影响表中数据:"+executeUpdate); //5.接收并处理结果集对象(ResultSet对象) //6.关闭资源 ResultSet PreparedStatement Connection 后开的先关 pstm.close(); conn.close(); }
实体类
一个实体类对应了数据库中的一张表
实体类中的一个属性对应了表中的一个字段
一个书体类对象对应了表中的一行数据
实体类的存放位置: com.baizhi.entity
public class BookDao { //添加 public void insert(Book b) { //获取数据库连接对象 Connection conn = null; //获取sql发送工具对象 PreparedStatement pstm = null; try { //加载驱动 Class.forName("oracle.jdbc.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr"); pstm = conn.prepareStatement("insert into t_book values(seq_book.nextval,?,?,?)"); //给占位符赋值 pstm.setString(1, b.getName()); pstm.setString(2, b.getAuthor()); pstm.setDouble(3, b.getPrice()); //发送sql pstm.executeUpdate(); } catch (Exception e) { // TODO Auto-generated catch block System.out.println("添加失败,原因为:"); e.printStackTrace(); } finally { //接收并处理结果集 //关闭资源 if (pstm != null) { try { pstm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } //根据id删除 public void delete(int id) { //获取数据库连接对象 Connection conn = null; //获取sql发送工具对象 PreparedStatement pstm = null; try { //加载驱动 Class.forName("oracle.jdbc.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr"); pstm = conn.prepareStatement("delete from t_book where book_id = ?"); //给占位符赋值 pstm.setInt(1, id); //发送sql pstm.executeUpdate(); } catch (Exception e) { // TODO Auto-generated catch block System.out.println("删除失败,原因为:"); e.printStackTrace(); } finally { //接收并处理结果集 //关闭资源 if (pstm != null) { try { pstm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } //修改 public void update(Book b) { Connection conn = null; PreparedStatement pstm = null; try { Class.forName("oracle.jdbc.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr"); pstm = conn.prepareStatement("update t_book set book_name=?,AUTHOR=?,PRICE=? where book_id=?"); pstm.setString(1, b.getName()); pstm.setString(2, b.getAuthor()); pstm.setDouble(3, b.getPrice()); pstm.setInt(4, b.getId()); pstm.executeUpdate(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if (pstm != null) { try { pstm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } //根据id查询一个 public Book selectById(int id) { Book book = null; Connection conn = null; PreparedStatement pstm = null; ResultSet rs = null; try { book = new Book(); Class.forName("oracle.jdbc.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr"); pstm = conn.prepareStatement("select * from t_book where book_id = ?"); pstm.setInt(1, id); rs = pstm.executeQuery(); if(rs.next()) { book.setId(rs.getInt("book_id")); book.setName(rs.getString("book_name")); book.setAuthor(rs.getString("author")); book.setPrice(rs.getDouble("price")); } } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (pstm != null) { try { pstm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return book; } //查询全部 public List<Book> selectAll(){ List<Book> list = new ArrayList<Book>(); Connection conn = null; PreparedStatement pstm = null; ResultSet rs = null; try { Class.forName("oracle.jdbc.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr"); pstm = conn.prepareStatement("select * from t_book"); rs = pstm.executeQuery(); while(rs.next()) { Book book = new Book(); book.setId(rs.getInt("book_id")); book.setName(rs.getString("book_name")); book.setAuthor(rs.getString("author")); book.setPrice(rs.getDouble("price")); list.add(book); } } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (pstm != null) { try { pstm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return list; } }
接口编程
com.baizhi.dao ------- dao的接口
com.baizhi.dao.impl ----- dao的实现类
com.baizhi.test ----- 测试类
位置:com.baizhi.util
public class JDBCUtil { //加载驱动.获取连接 public static Connection getConn() { Connection conn = null; try { Class.forName("oracle.jdbc.OracleDriver"); conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "hr"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return conn; } //关闭资源 public static void closeAll(Connection conn,PreparedStatement pstm,ResultSet rs) { if(rs != null) { try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(pstm != null) { try { pstm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
public class JDBCUtil { //加载驱动.获取连接 public static Connection getConn() { Connection conn = null; try { //创建properties Properties pro = new Properties(); //获取输入流 InputStream is = JDBCUtil.class.getResourceAsStream("/JDBCUtil.properties"); //读取JDBCUtil.properties pro.load(is); //获取键值对 String className = pro.getProperty("className"); String url = pro.getProperty("url"); String user = pro.getProperty("user"); String pwd = pro.getProperty("pwd"); Class.forName(className); conn = DriverManager.getConnection(url, user, pwd); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return conn; } //关闭资源 public static void closeAll(Connection conn,PreparedStatement pstm,ResultSet rs) { if(rs != null) { try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(pstm != null) { try { pstm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
-
每次调用getConnection，都会执行一遍读取配置文件操作，影响程序的运行效率
解决方案：
将读取配置文件的操作，放置到静态代码块中，保证读取文件的操作有且必须执行1遍
public class JDBCUtil { private static String className; private static String url; private static String user; private static String pwd; static { //创建properties Properties pro = new Properties(); //获取输入流 InputStream is = JDBCUtil.class.getResourceAsStream("/JDBCUtil.properties"); //读取JDBCUtil.properties try { pro.load(is); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } //获取键值对 className = pro.getProperty("className"); url = pro.getProperty("url"); user = pro.getProperty("user"); pwd = pro.getProperty("pwd"); //关流 try { is.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //加载驱动.获取连接 public static Connection getConn() { Connection conn = null; try { Class.forName(className); conn = DriverManager.getConnection(url, user, pwd); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return conn; } //关闭资源 public static void closeAll(Connection conn,PreparedStatement pstm,ResultSet rs) { if(rs != null) { try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(pstm != null) { try { pstm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } if(conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
视图层(view)
作用:完成与用户的交互,接收用户输入的信息,向用户展示信息
技术:HTML CSS JS JQuery....目前使用主函数代替
业务层(service)
作用:根据功能需求,调用dao,完成逻辑判断
编码过程:马上学
持久层(dao)
作用:将数据持久化的保存在数据库,从数据库提取数据
编码过程:已经学了
业务层详解
存放位置:com.baizhi.service --- service的接口
com.baizhi.service.impl --- service的实现类
项目编程步骤
1.建项目
2.导入jar包
3.导入工具类
a. JDBCUtil.java ------ com.baizhi.util
b.JDBCUtil.properties ----- src
4.建表
5.建实体类 ------ com.baizhi.entity
6.dao
a.dao的接口 ------ com.baizhi.dao
b.dao的实现类 ----- com.baizhi.dao.impl
7.测试dao
a. 测试类 ------- com.baizhi.test
8.service
a.service的接口 ---- com.baizhi.service
b.service的实现类 ----- com.baizhi.service.impl
9.测试service
a. 测试类 ------- com.baizhi.test
service事务控制
什么叫事务
含义:在一个功能(一系列的sql操作),要么全部成功,要么全部失败
例:
范大头老师在5月20号时,要给女朋友转500元,为了过光棍劫
service{
转账(){
try{
conn.setAutoCommit(false);
//1.从范大头的账户中-500元
//2.给女朋友+500元
conn.commit();
}catch(Exception e){
conn.rollback();
throw new RuntimeException(e);
}
}
}
JDBC控制事务
jdbc的事务控制
默认每完成一次sql操作,自动提交一次事务
手动控制事务
开启手动控制事务: conn.setAutoCommit(false);
提交事务: conn.commit();
回滚事务: conn.rollback();
解决service和dao的连接不相同问题
方法一:将conn作为dao方法中的参数传入
interface XXDao{
void insert(User user, Conncetion conn);
}
class XXservice{
insert(User user){
Conncetion conn = JDBCUtil.getConn()
XXDao dao = new XXDaoImpl();
dao.insert(user,conn);
}
}缺点:
1.将连接对象定义在了Dao方法声明中,强耦合
2.修改的代码太多了
方法二 : Threadlocal(储存空间)
1.有效范围
在同一个线程中有效 ---- 存的时间
存一个键值对(线程对象,value) ---- 存内容的多少2.怎么存(方法)
tl.set(value)3.怎么取(方法)
tl.get();4.怎么移除(方法)
tl.remove();JDBCUtilFinal
public class JDBCUtil { private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>(); private static String className; private static String url; private static String user; private static String pwd; static { //创建properties Properties pro = new Properties(); //获取输入流 InputStream is = JDBCUtil.class.getResourceAsStream("/JDBCUtil.properties"); //读取JDBCUtil.properties try { pro.load(is); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } //获取键值对 className = pro.getProperty("className"); url = pro.getProperty("url"); user = pro.getProperty("user"); pwd = pro.getProperty("pwd"); } //加载驱动.获取连接 public static Connection getConn() { if (tl.get() == null) { try { Class.forName(className); Connection conn = DriverManager.getConnection(url, user,pwd); tl.set(conn); } catch (Exception e) { throw new RuntimeException(e); } } return tl.get(); } //关闭资源 public static void closeAll(Connection conn,PreparedStatement pstm,ResultSet rs) { if(rs != null) { try { rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block throw new RuntimeException(e); } } if(pstm != null) { try { pstm.close(); } catch (SQLException e) { // TODO Auto-generated catch block throw new RuntimeException(e); } } if(conn != null) { try { conn.close(); tl.remove(); } catch (SQLException e) { // TODO Auto-generated catch block throw new RuntimeException(e); } } } }
1.在dao中不能关闭conn
2.在service中关闭conn
JUint单元测试
步骤:
1.添加@Test注解,方法上添加
2.引入JUnit jar包
3.定义方法
要求: 无参无返回值,方法名随意的方法
4.运行方法时.点击方法名,右键------runs ------ Junit Test
注意事项
1.类名 方法名都不要叫Test