Java40: 数据库四(Oracle)
短信预约 -IT技能 免费直播动态提醒
JDBC 提供访问数据库的标准 就是一系列接口 定义了访问数据库的通用的方法
由各个数据库厂商提供对JDBC的实现
JDBC接口的定义
数据库厂商对jdbc的实现 jar
建立连接-发送sql-执行sql-返回结果-关闭连接
JDBC API（接口）
java.sql.Connection//封装和数据库的连接
java.sql.Statement//封装SQL语句的执行
jave.sql.ResultSet//封装DQL执行的结果
package jdbc;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.OracleDriver;
public class OracleDemo01 {
public static void main(String[] args) throws Exception{
//装载JDBC的实现
//Driver driver =new OracleDriver() ; 自己写
//DriverManager.registerDriver(driver);
//把类装载到内存中 静态 快中执行 上面的两条 就不用自己写了
//该类的静态块中的代码会进行Driver的注册
Class.forName("oracle.jdbc.OracleDriver");//装载JDBC的实现用这个就好了
//创建连接
//调用DriverManager 的getConnection方法
//该方法返回的是数据库厂商对Connection接口的实现类的对象(因为DriverManager已经注册了数据库厂商的Driver信息)
String url = "jdbc:oracle:thin:@x1.zongxuan.online:1521:xx";
String user = "scott";
String pass = "xxxxx";
Connection con = DriverManager.getConnection(url,user,pass);
//url 用于表示数据库的连接信息(ip port 数据库名称) 不同的数据库厂商有特定的url的格式和标识
// DriverManager 会根据这个标识来选取不同的驱动信息
//如果同时注册了多个数据库厂商的实现
//user
//password
//System.out.println(con);
//执行SQL
//Connection 的createStatement() 方法用于创建Statement 实现类的对象
Statement stmt = con.createStatement();
//只能执行DQL语句
//返回值是ResultSet
//把SQl语句传输给数据库执行
//获取数据库传输会的结果数据
//并把这个数据封装成ResultSet 对象
ResultSet rs = stmt.executeQuery("select empno,ename name,sal from emp");
//获取结果
while(rs.next()){//.next() 游标 向下
System.out.println(rs.getString("empno")+","+rs.getString("name")+","+rs.getString("sal"));
}
rs.close();
//关闭连接
}
}package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class OracleDemo02 {
public static void main(String[] args) throws Exception{
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@x1.zongxuan.online:1521:xx";
String user="scott";
String pass="xxxxx";
Connection con = DriverManager.getConnection(url,user,pass);
Statement stmt = con.createStatement();
//执行DML语句 insert delete update
String sql = "insert into emp2(empno,ename,sal,deptno) values(20,'lmdtx',3000,10)";
int n = stmt.executeUpdate(sql);//返回整数表示，刚才的语句影响的行数
System.out.println(n);
con.close();
}
}package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class OracleDemo03 {
public static void main(String[] args) throws Exception{
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@x1.zongxuan.online:1521:xx";
String user = "scott";
String pass = "xxxxx";
Connection con = DriverManager.getConnection(url,user,pass);
Statement stmt = con.createStatement();
String sql = "delete from emp2 where ename='lmdtx'";
int n = stmt.executeUpdate(sql);
System.out.println(n);
con.close();
}
}会有被注入的风险
name:a' or 'b'='b
a' or 'b'='b
select ename,empno,sal from emp2 where ename = 'a' or 'b'='b'package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class OracleDemo04 {
public static void main(String[] args) throws Exception{
Scanner in = new Scanner(System.in);
System.out.print("name:");
String name = in.nextLine();
System.out.println(name);
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@x1.zongxuan.online:1521:xx";
String user = "scott";
String pass = "xxxxx";
Connection con = DriverManager.getConnection(url,user,pass);
Statement stat = con.createStatement();
String sql ="select ename,empno,sal from emp2 where ename = '"+name+"'";
System.out.println(sql);
ResultSet rs = stat.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString(1)+","+rs.getInt(2)+","+rs.getInt(3));
}
con.close();
}
}PreparedStatement 接口（用这个好）
提高效率
防止SQL Injection
1创建连接
2PreparedStatement
PreparedStatement stmt = con.prepareStatement(sql)
3 stmt.setString(1,"xxx");
stmt.setInt(1,123);
package jdbc;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class OracleDemo05 {
public static void main(String[] args)throws Exception{
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
System.out.println("name:");
String name = br.readLine();
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@x1.zongxuan.online:1521:xx";
String user = "scott";
String pass = "xxxxx";
Connection con = DriverManager.getConnection(url,user,pass);
String sql = "select empno,ename,sal from emp2 where ename=?";
PreparedStatement stmt = con.prepareStatement(sql);
//将 sql 中 第一个问号的值设置为字符串
stmt.setString(1,name);
ResultSet rs = stmt.executeQuery();
while(rs.next()){
System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getInt(3));
}
con.close();
}
}
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class OracleDemo06 {
public static void main(String[] args) throws Exception{
Class.forName("oracle.jdbc.OracleDriver");
String url ="jdbc:oracle:thin:@x1.zongxuan.online:1521:xx";
String user ="scott";
String pass = "xxxxx";
Connection con = DriverManager.getConnection(url,user,pass);
String sql ="insert into emp2(empno,ename) values(?,?)";
PreparedStatement stmt = con.prepareStatement(sql);
int n =0;
for(int i =1000;i<3000;i++){
stmt.setInt(1, i+1);
stmt.setString(2, "sting"+i);
n = stmt.executeUpdate();
System.out.println(i+","+n);
}
con.close();
}
}封装连接工具类
properties
文本文件， 存放的是KEY—VALUSE
driver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@x1.zongxuan.online:1521:xx
user=scott
pass=xxxxxpackage jdbc;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
public class DBUtils {
private static String driver;
private static String url;
private static String user;
private static String pass;
static{
Properties props = new Properties();
try {
props.load(DBUtils.class.getClassLoader().getResourceAsStream("jdbc/db.properties"));
driver= props.getProperty("driver");
url= props.getProperty("url");
user= props.getProperty("user");
pass= props.getProperty("pass");
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection openConnection() throws Exception{
return DriverManager.getConnection(url,user,pass);
}
}package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class OracleDemo08 {
public static void main(String[] args) throws Exception {
Connection con = DBUtils.openConnection();
String sql = "select empno,ename,sal from emp2";
PreparedStatement stmt = con.prepareStatement(sql);
// 将 sql 中 第一个问号的值设置为字符串
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt(1) + "," + rs.getString(2) + ","
+ rs.getInt(3));
}
con.close();
}
}日期
package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Date;
public class Oracledemo09 {
public static void main(String[] args) throws Exception{
Connection con = DBUtils.openConnection();
String sql ="insert into emp2(empno,ename,hiredate)"
+ "values(?,?,?)";
PreparedStatement stmt =con.prepareStatement(sql);
stmt.setInt(1, 4000);
stmt.setString(2,"lmdtx");
stmt.setDate(3,new Date(System.currentTimeMillis()));
int n =stmt.executeUpdate();
System.out.println(n);
con.close();
}
}批处理
Batch 处理 批量的插入 更新
stmt.addBatch();
把刚刚设置好的数据添加批处理缓冲
stmt
执行批处理，把缓冲的数据一次传送给数据库执行
缓存的批处理量受到哭护短JVM内存的限制要指定一个合理的批量值才好
package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class OracleDemo10 {
public static void main(String[] args) throws Exception{
Connection con = DBUtils.openConnection();
String sql = "insert into emp2(empno,ename) values(?,?)";
PreparedStatement stmt = con.prepareStatement(sql);
//批量插入
for(int i =5000;i<=6000;i++){
stmt.setInt(1, i);
stmt.setString(2, "M"+i);
stmt.addBatch();//添加批处理
}
stmt.executeBatch();//执行批处理
con.close();
}
}package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class OracleDemo10 {
public static void main(String[] args) throws Exception{
Connection con = DBUtils.openConnection();
String sql = "insert into emp100(id,name) values(?,?)";
PreparedStatement stmt = con.prepareStatement(sql);
//批量插入
for(int i =1;i<=2000000;i++){
stmt.setInt(1, i);
stmt.setString(2, "M"+i);
stmt.addBatch();//添加批处理
if(i%20000==0){
stmt.executeBatch();
}
}
stmt.executeBatch();//执行批处理
con.close();
}
}JDBC的事物操作
JDBC 中所有的DML会默认提交 在每条DML语句后会默认的加入commit
con.setAutoCommit(false)
con.commit();//成功
con.rollback();//失败
提交事务使用 连接的api
测试 表 pay id 10 和id 100
id 10 的人向 id 100 的人 转账500
create table pay(
id number(10),
money number(10)
);
insert into pay values(10,20000);
insert into pay values(100,10000);
select * from pay;package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class OracleDemo11 {
public static void main(String[] args) throws Exception {
Connection con = DBUtils.openConnection();
PreparedStatement stmt = con.prepareStatement("update pay set money = money-500 where id=10");
int n = stmt.executeUpdate();
System.out.println(n+"行"+"id = 10 money -500");
if (true) {
throw new Exception("模仿异常");
}
PreparedStatement stmt2 = con.prepareStatement("update pay set money = money+500 where id=100");
stmt2.executeUpdate();
System.out.println("id = 100 money +500");
con.close();
}
}这个就会有异常， id =10的钱被-了500 但是id =100 的却没有+500
使用con.setAutoCommit(false)
package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class OracleDemo12 {
public static void main(String[] args) throws Exception{
Connection con =null;
try{
con = DBUtils.openConnection();
con.setAutoCommit(false);
PreparedStatement stmt = con.prepareStatement("update pay set money = money-500 where id=10");
int n = stmt.executeUpdate();
System.out.println(n+"行"+"id = 10 money -500");
if (true) {
throw new Exception("模仿异常");
}
PreparedStatement stmt2 = con.prepareStatement("update pay set money = money+500 where id=100");
stmt2.executeUpdate();
System.out.println("id = 100 money +500");
con.commit();
}catch(Exception e){
con.rollback();
e.printStackTrace();
}
finally{
if(con!=null){
con.close();
}
}
}
}package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class OracleDemo12 {
public static void main(String[] args) throws Exception{
Connection con =null;
try{
con = DBUtils.openConnection();
con.setAutoCommit(false);
PreparedStatement stmt = con.prepareStatement("update pay set money = money-500 where id=10");
int n = stmt.executeUpdate();
System.out.println(n+"行"+"id = 10 money -500");
PreparedStatement stmt2 = con.prepareStatement("update pay set money = money+500 where id=100");
stmt2.executeUpdate();
System.out.println("id = 100 money +500");
con.commit();
}catch(Exception e){
con.rollback();
e.printStackTrace();
}
finally{
if(con!=null){
con.close();
}
}
}
}连接池技术
别自己写 用开源的
Oracle
weblogic
阿里
Druid
Apache Commons
DBCP
POOL
LOGGING
连接池是一个软件 管理一系列已经建立好的连接 可以节省连接频繁建立的开销，一些牛X的连接池还可以根据当时的用户访问量动态的改变所管理连接的数目
在应用了连接池以后
con.close（）
只是将连接 还给连接池，不在是真正的关闭掉了
package jdbc;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import org.apache.commons.dbcp2.BasicDataSource;
public class DBUtils2 {
private static String driver;
private static String url;
private static String user;
private static String pass;
private static BasicDataSource ds;
static{
Properties props = new Properties();
try {
props.load(DBUtils.class.getClassLoader().getResourceAsStream("jdbc/db.properties"));
driver= props.getProperty("driver");
url= props.getProperty("url");
user= props.getProperty("user");
pass= props.getProperty("pass");
ds = new BasicDataSource();
ds.setDriverClassName(driver);
ds.setUrl(url);
ds.setUsername(user);
ds.setPassword(pass);
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws Exception{
return ds.getConnection();
}
}package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class OracleDemo13 {
public static void main(String[] args) throws Exception{
Connection con = DBUtils2.getConnection();
String sql = "insert into emp100(id,name) values(?,?)";
PreparedStatement stmt = con.prepareStatement(sql);
//批量插入
for(int i =1;i<=10000000;i++){
stmt.setInt(1, i);
stmt.setString(2, "M"+i);
stmt.addBatch();//添加批处理
if(i%20000==0){
stmt.executeBatch();
System.out.println(i);
}
}
stmt.executeBatch();//执行批处理
con.close();
}
}DAO
Data Access Object
专门用来访问数据库的程序=》 数据访问层 （Data Access Layer）
1》实体类
和数据表对应的类，一般只包含数据和对数据的基本访问方法(setter,getter)
一般情况下：一个实体类对应一个表
一个实体类的对象 对应表中的一行数据
2》DAO类
负责对数据库的CRUD（增删改查）
封装所有对数据库的操作 供其它模块的程序调用
一帮情况是 一个表对一个DAO类
DAO 中有很多的方法，每个方法都是对数据库的一种操作
3》数据连接工具
用于封装对数据库连接的获取
完整的参考下面（ 第一个 ） 后面还有改版.
driver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@c1.zongxuan.online:1521:xx
user=scott
pass=xxxxxpackage jdbc;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import org.apache.commons.dbcp2.BasicDataSource;
public class DBUtils {
private static String driver;
private static String url;
private static String user;
private static String pass;
private static BasicDataSource ds;
static{
Properties props = new Properties();
try {
props.load(DBUtils.class.getClassLoader().getResourceAsStream("jdbc/db.properties"));
driver= props.getProperty("driver");
url= props.getProperty("url");
user= props.getProperty("user");
pass= props.getProperty("pass");
ds = new BasicDataSource();
ds.setDriverClassName(driver);
ds.setUrl(url);
ds.setUsername(user);
ds.setPassword(pass);
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws Exception{
return ds.getConnection();
}
public static void main(String[] args) throws Exception{
Connection con = getConnection();
System.out.println(con);
}
}package jdbc;
public class Dept {
private int deptno;
private String dname;
private String loc;
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
}package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class DeptDAO {
private static final String FIND_ALL="select deptno,dname,loc from dept";
private static final String FIND_BY_LOC="select deptno,dname,loc from dept where loc=?";
private static final String ADD="insert into dept(deptno,dname,loc) values(?,?,?)";
public List<Dept> finaALL() throws Exception{
List<Dept> list;
Connection con = null;
try {
con = DBUtils.getConnection();
PreparedStatement stmt = con.prepareStatement(FIND_ALL);
ResultSet rs = stmt.executeQuery();
list = new ArrayList<Dept>();
while(rs.next()){
list.add(toDept(rs));
}
return list;
} catch (Exception e) {
e.printStackTrace();
throw e;
}finally{
if(con != null){
con.close();
}
}
}
public List<Dept> findByLoc(String loc) throws Exception{
List<Dept> list;
Connection con = null;
try {
con = DBUtils.getConnection();
PreparedStatement stmt = con.prepareStatement(FIND_BY_LOC);
stmt.setString(1,loc);
ResultSet rs = stmt.executeQuery();
list = new ArrayList<Dept>();
while(rs.next()){
list.add(toDept(rs));
}
return list;
} catch (Exception e) {
e.printStackTrace();
throw e;
}finally{
if(con != null){
con.close();
}
}
}
public void Add(Dept dept) throws Exception{
Connection con = null;
try{
con = DBUtils.getConnection();
PreparedStatement stmt = con.prepareStatement(ADD);
stmt.setInt(1, dept.getDeptno());
stmt.setString(2, dept.getDname());
stmt.setString(3, dept.getLoc());
stmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
throw e;
}
}
private Dept toDept(ResultSet rs) throws SQLException{
Dept dept = new Dept();
dept.setDeptno(rs.getInt("deptno"));
dept.setDname(rs.getString("dname"));
dept.setLoc(rs.getString("loc"));
return dept;
}
}package jdbc;
import java.math.BigDecimal;
import java.sql.Date;
public class Emp {
//FIND_ALL="select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp";
private int empno;
private String ename;
private Date hireDate;
private BigDecimal sal;
private String job;
private int mgr;
private int comm;
private int deptno;
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Date getHireDate() {
return hireDate;
}
public void setHireDate(Date hireDate) {
this.hireDate = hireDate;
}
public BigDecimal getSal() {
return sal;
}
public void setSal(BigDecimal sal) {
this.sal = sal;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public int getComm() {
return comm;
}
public void setComm(int comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
}package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class EmpDAO {
private static final String FIND_ALL="select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp";
private static final String FIND_BY_LOC="select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename=?";
private static final String ADD="insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(?,?,?,?,?,?,?,?)";
public List<Emp> finaALL() throws Exception{
List<Emp> list;
Connection con = null;
try {
con = DBUtils.getConnection();
PreparedStatement stmt = con.prepareStatement(FIND_ALL);
ResultSet rs = stmt.executeQuery();
list = new ArrayList<Emp>();
while(rs.next()){
list.add(toEmp(rs));
}
return list;
} catch (Exception e) {
e.printStackTrace();
throw e;
}finally{
if(con != null){
con.close();
}
}
}
public List<Emp> findByLoc(String ename) throws Exception{
List<Emp> list;
Connection con = null;
try {
con = DBUtils.getConnection();
PreparedStatement stmt = con.prepareStatement(FIND_BY_LOC);
stmt.setString(1,ename);
ResultSet rs = stmt.executeQuery();
list = new ArrayList<Emp>();
while(rs.next()){
list.add(toEmp(rs));
}
return list;
} catch (Exception e) {
e.printStackTrace();
throw e;
}finally{
if(con != null){
con.close();
}
}
}
public void Add(Emp emp) throws Exception{
Connection con = null;
try{
con = DBUtils.getConnection();
PreparedStatement stmt = con.prepareStatement(ADD);
stmt.setInt(1, emp.getEmpno());
stmt.setString(2, emp.getEname());
stmt.setString(3, emp.getJob());
stmt.setInt(4, emp.getMgr());
stmt.setDate(5, emp.getHireDate());
stmt.setBigDecimal(6, emp.getSal());
stmt.setInt(7, emp.getComm());
stmt.setInt(8, emp.getDeptno());
stmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
throw e;
}
}
//FIND_ALL="select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp";
private Emp toEmp(ResultSet rs) throws SQLException{
Emp emp = new Emp();
emp.setEmpno(rs.getInt("empno"));
emp.setEname(rs.getString("ename"));
emp.setJob(rs.getString("job"));
emp.setMgr(rs.getInt("mgr"));
emp.setHireDate(rs.getDate("hiredate"));
emp.setSal(rs.getBigDecimal("sal"));
emp.setComm(rs.getInt("comm"));
emp.setDeptno(rs.getInt("deptno"));
return emp;
}
}package jdbc;
import org.junit.Test;
public class TestDeptDAO {
@Test
public void testFinadAll() throws Exception{
for(Dept l:new DeptDAO().finaALL()){
System.out.println(l.getDeptno()+","+l.getDname()+","+l.getLoc());
}
}
@Test
public void testFinadByLoc() throws Exception{
for(Dept l:new DeptDAO().findByLoc("DALLAS")){
System.out.println(l.getDeptno()+","+l.getDname()+","+l.getLoc());
}
}
@Test
public void testAdd() throws Exception{
Dept dept = new Dept();
dept.setDeptno(60);
dept.setDname("RD");
dept.setLoc("SH");
new DeptDAO().Add(dept);
}
}package jdbc;
import org.junit.Test;
public class TestEmpDAO {
// private static final String FIND_ALL="select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp";
@Test
public void test() throws Exception {
for(Emp l:new EmpDAO().finaALL()){
System.out.println(l.getEmpno()+","+l.getEname()+","+l.getJob()+","+l.getMgr()+","+l.getHireDate()+","+l.getSal()+","+l.getComm()+","+l.getDeptno());
}
}
}优化一下：
driver=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@c1.zongxuan.online:1521:xx
user=scott
pass=xxxxxpackage jdbc;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import org.apache.commons.dbcp2.BasicDataSource;
public class DBUtils {
private static String driver;
private static String url;
private static String user;
private static String pass;
private static BasicDataSource ds;
static{
Properties props = new Properties();
try {
props.load(DBUtils.class.getClassLoader().getResourceAsStream("jdbc/db.properties"));
driver= props.getProperty("driver");
url= props.getProperty("url");
user= props.getProperty("user");
pass= props.getProperty("pass");
ds = new BasicDataSource();
ds.setDriverClassName(driver);
ds.setUrl(url);
ds.setUsername(user);
ds.setPassword(pass);
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws Exception{
return ds.getConnection();
}
public static void main(String[] args) throws Exception{
Connection con = getConnection();
System.out.println(con);
}
}package jdbc;
import java.math.BigDecimal;
import java.sql.Date;
public class Emp {
//FIND_ALL="select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp";
private int empno;
private String ename;
private Date hireDate;
private BigDecimal sal;
private String job;
private int mgr;
private int comm;
private int deptno;
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public Date getHireDate() {
return hireDate;
}
public void setHireDate(Date hireDate) {
this.hireDate = hireDate;
}
public BigDecimal getSal() {
return sal;
}
public void setSal(BigDecimal sal) {
this.sal = sal;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public int getMgr() {
return mgr;
}
public void setMgr(int mgr) {
this.mgr = mgr;
}
public int getComm() {
return comm;
}
public void setComm(int comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
}package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public abstract class BaseDAO {
//封装查询 修饰符protected
//sql语句
//参数
protected List query(String sql,Object[] o) throws Exception{
Connection con = null;
try {
con = DBUtils.getConnection();
PreparedStatement stmt = con.prepareStatement(sql);
if(o != null){
for(int i =0;i<o.length;i++){
stmt.setObject(i+1, o[i]);//JDBC 帮你判断具体的参数类型
}
}
ResultSet rs = stmt.executeQuery();
List list = new ArrayList();
while(rs.next()){
list.add(toEntity(rs));
}
return list;
} catch (Exception e) {
e.printStackTrace();
throw e;
}finally{
if(con != null){
con.close();
}
}
}
//不同的子类用不通的方法吧结果集中的数据转换
public abstract Object toEntity(ResultSet rs) throws Exception;
}package jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class EmpDAO extends BaseDAO {
private static final String FIND_ALL="select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp";
private static final String FIND_BY_ENAME="select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename=?";
private static final String ADD="insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(?,?,?,?,?,?,?,?)";
public Object toEntity(ResultSet rs) throws Exception{
Emp emp = new Emp();
emp.setEmpno(rs.getInt("empno"));
emp.setEname(rs.getString("ename"));
emp.setJob(rs.getString("job"));
emp.setMgr(rs.getInt("mgr"));
emp.setHireDate(rs.getDate("hiredate"));
emp.setSal(rs.getBigDecimal("sal"));
emp.setComm(rs.getInt("comm"));
emp.setDeptno(rs.getInt("deptno"));
return emp;
}
public List<Emp> finadAll() throws Exception{
return (List<Emp>)query(FIND_ALL, null);
}
public List<Emp> finadByEname(String ename) throws Exception{
return (List<Emp>)query(FIND_BY_ENAME,new Object[]{ename});
}
}package jdbc;
import org.junit.Test;
public class TestEmpDAO {
// private static final String FIND_ALL="select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp";
@Test
public void testFinadAll() throws Exception {
for(Emp l:new EmpDAO().finadAll()){
System.out.println(l.getEmpno()+","+l.getEname()+","+l.getJob()+","+l.getMgr()+","+l.getHireDate()+","+l.getSal()+","+l.getComm()+","+l.getDeptno());
}
}
@Test
public void testFinaByLoc() throws Exception {
for(Emp l:new EmpDAO().finadByEname("SCOTT")){
System.out.println(l.getEmpno()+","+l.getEname()+","+l.getJob()+","+l.getMgr()+","+l.getHireDate()+","+l.getSal()+","+l.getComm()+","+l.getDeptno());
}
}
}免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/9.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
