2020.8.22JDBC原理以及Dao分层

1.JDBC访问数据库步骤

public class BaseDao{

public connection getConnection(){

//加载驱动：

Class.forName("com.mysql.jdbc.Driver");

String url="jdbc:mysql:192.168l.183.31/数据库名;

//驱动管理获取连接

Connection connection=DriverManager.getConnection(url,"用户名","密码");

return connection;

}

//查询表结构方法

public void showTables(){

Connection connection=getConnection;

Statement stmt=connection.createStatement;

String str="show tables";

RestultSet rs=stmt.executeQuery(sql)

while(rs.next()){

system.out.println(rs.getString("Tables_in_Myschool"))//用标签查询表结构

//system.out.println(rs.getString("1"))//用索引查询表结构

    }

}

//通用查询方法

public ResultSet query(String sql) throwsException{

Connection connection =getConnection();

Statement stmt=connection.createStatement();

ResultSet rs=stmt.executeQuery(sql);

return rs;

    }

//查询方法进阶一

public ResultSet query(String sql,int id,String name) throwsException{

Connection connection =getConnection();

PreparedStatement pst=connection.prepareStatement(sql)

pst.setObject(1,id);

pst.setObject(2,name);

ResultSet rs=pst.executeQuery();

return rs;

    }

//增删改方法

public int update(String sql)throws Exception{

Connection connection=getConnection();

Statement  stmt=connection.createStatement;

int num=stmt.executeUpate(sql);

return num

    }

}

注：此时需要对要查询的表创建一个类，定义它的字段

例如要查询student表

public class student{

private int StudentNo;

private int GradeId;

private String StudentName;

private String sex;

private int GradeId;

private String Phone;

private String Address;

private String BornDate;

private String Email;

private String IdentityCard;

@Override

public String toString() {

return "Student{" +

"StudentNo=" + StudentNo +

", StudentName='" + StudentName + '\'' +

", sex='" + sex + '\'' +

", GradeId=" + GradeId +

", Phone='" + Phone + '\'' +

", Address='" + Address + '\'' +

", BornDate='" + BornDate + '\'' +

", Email='" + Email + '\'' +

", IdentityCard='" + IdentityCard + '\'' +

'}';

}

 

public int getStudentNo() {

return StudentNo;

}

 

public void setStudentNo(int studentNo) {

StudentNo = studentNo;

}

 

public String getStudentName() {

return StudentName;

}

 

public void setStudentName(String studentName) {

StudentName = studentName;

}

 

public String getSex() {

return sex;

}

 

public void setSex(String sex) {

this.sex = sex;

}

 

public int getGradeId() {

return GradeId;

}

 

public void setGradeId(int gradeId) {

GradeId = gradeId;

}

 

public String getPhone() {

return Phone;

}

 

public void setPhone(String phone) {

Phone = phone;

}

 

public String getAddress() {

return Address;

}

 

public void setAddress(String address) {

Address = address;

}

 

public String getBornDate() {

return BornDate;

}

 

public void setBornDate(String bornDate) {

BornDate = bornDate;

}

 

public String getEmail() {

return Email;

}

 

public void setEmail(String email) {

Email = email;

}

 

public String getIdentityCard() {

return IdentityCard;

}

 

public void setIdentityCard(String identityCard) {

IdentityCard = identityCard;

}

}

再定义测试类：

public class BaseDaoTest{

//获取连接

@Test

public getConnection ()throws Exception{

    BaseDao dao=new BaseDao();

    Connection connection=dao.getConnection();

}

//显示表结构

@Test

public void showTables(){

BaseDao dao=new BaseDao();

dao.showTables();

}

//通用查询，例如查询学生类

@test

public void query()throws Exception{

BaseDao dao=new BaseDao();

String sql="select * from student";

ResultSet rs=dao.query(sql);

ArrayList<Student>subList=new ArrayList<>();

while(rs.next()){

Student s=new Student();

//注意这里的StudentNo要跟数据库中的字段名相同，类型也要一致，一一对应

s.setStudentNo(rs.getInt("StudentNo"))；

s.setStudentName(rs.getString("StudentName"));

s.estPhone(rs.getString("Phone"));

s.setSex(rs.getString("Sex"));

s.setGradeId(rs.getInt("GradeId"));

s.setAddress(rs.getString("Address"));

s.setEmail(rs.getString("Email"));

s.setBornDate(rs.getString("BornDate"));

s.setIdentityCard(rs.getString("IdentityCard"));

subList.add(s);

    }

for(Student student: subList){

system.out.println(student);

}

//进阶查询学生表

@test

public void query()throws Exception{

BaseDao dao=new BaseDao();

String sql="select * from student where StudentNo=? or StudentName like ?";

ResultSet rs=dao.query(sql,1001,"李%");

ArrayList<Student>subList=new ArrayList<>();

while(rs.next()){

Student s=new Student();

//注意这里的StudentNo要跟数据库中的字段名相同，类型也要一致，一一对应

s.setStudentNo(rs.getInt("StudentNo"))；

s.setStudentName(rs.getString("StudentName"));

s.estPhone(rs.getString("Phone"));

s.setSex(rs.getString("Sex"));

s.setGradeId(rs.getInt("GradeId"));

s.setAddress(rs.getString("Address"));

s.setEmail(rs.getString("Email"));

s.setBornDate(rs.getString("BornDate"));

s.setIdentityCard(rs.getString("IdentityCard"));

subList.add(s);

    }

for(Student student: subList){

system.out.println(student);

}

//增

@Test

public void update()throws Exception{

    BaseDao dao=new BaseDao();

    String sql="insert into Student(StudentNo,StudentName)

values(1,"王五")"；

int num=dao.update(sql);

system.out.println(num>0?"插入成功":"插入失败")；

}

//创建数据库和表

public void testDao()throws Exception{

BaseDao dao=new BaseDao();

Connection connection=dao.getconnection();

Statement stmt=connection.createStatement();

String createDatabase="create database if not exists user";

stmt.excute(createDatebase);

String createTable="create table if not exists user_info

(uid int(11) auto_incremnet primary key, name varchar(30))";

stmt.excute(createTable);

    }

}

//进阶查询2

//1.在BaseDao添加新的方法

public void close(Connection connection,Statement stmt,ResultSet rs)throws Exception{

if(connection!=null){

connection.close();

    }

if(stmt!=null){

    stmt.close();

    }

if(rs!=null){

    rs.close();

    }

}

//2.在BaseDaoTest中的getConnection方法中添加

dao.close(connection,null,null);

并在query方法最后加上:   dao.close(null,null,rs);

3.//创建新的类PstDao

public class PstDao{

//建立连接

public static Connection getConnection(String url,String user,String pwd)

Class.forName("com.mysql.jdbc.Driver");

return DriverManager.getConnection(url,user,pwd);

return null;

}

//关闭数据流

public static void close(Connection connection,PreparedStatement pst,ResultSet rs){

if(connection!=null){

connection.close();}

if(pst!=null){

    pst.close();

    }

if(rs!=null){

    rs.close();

    }

//查询

public static ResultSet query(String sql,String url,String user,String pwd,Object ...params){

Connection connection=getConnection(url,user,pwd);

PreparedStatement pst=null;

ResultSet rs=null;

pst=connection.prepareStatement(sql);

for(int i=0;i<params.length;i++){

pst.setObject(i+1,params[i]);

        } 

rs=pst.executeQuery();

return rs;

    }

}

//4.创建PstDaoTest类

public class PstDaoTest{

@Test

public void query()throws SQLException{

        String sql="select SubjectName,.StudentResult from subject s left join result r on s.SubjcteNo=r.SubjectNo where StudentResult>? and 

SubjectName in("体育","英语")"；

String url="jdbc:mysql://192.168.183.31:3306/Myschool";

ResultSet rs=PstDao.query(sql,url,"root","ok",60,"体育","英语");

while(rs.next()){

System.out.print(rs.getString("SubjectName"));

System.out.print(rs.getString("SudentResult"));

}

rs.close();

        }

 

    }

//进阶查询3

public class PstDao{

//建立连接

private static string Driver="com.mysql.jdbc.Driver"

private static String url="jdbc:mysql://192.168.183.31:3306/Myschool";

private static String user="root";

private static String pwd="ok";

public static Connection getConnection()

Class.forName(driver);

return DriverManager.getConnection(url,user,pwd);

return null;

}

//关闭数据流

public static void close(Connection connection,PreparedStatement pst,ResultSet rs){

if(connection!=null){

connection.close();}

if(pst!=null){

    pst.close();

    }

if(rs!=null){

    rs.close();

    }

//查询

public static ResultSet query(String sql,Object ...params){

Connection connection=getConnection();

PreparedStatement pst=null;

ResultSet rs=null;

pst=connection.prepareStatement(sql);

for(int i=0;i<params.length;i++){

pst.setObject(i+1,params[i]);

        } 

rs=pst.executeQuery();

return rs;

    }

}

//4.创建PstDaoTest类

public class PstDaoTest{

@Test

public void query()throws SQLException{

        String sql="select SubjectName,.StudentResult from subject s left join result r on s.SubjcteNo=r.SubjectNo where StudentResult>? and 

SubjectName in("体育","英语")"；

String url="jdbc:mysql://192.168.183.31:3306/Myschool";

ResultSet rs=PstDao.query(sql,60,"体育","英语");

while(rs.next()){

System.out.print(rs.getString("SubjectName"));

System.out.print(rs.getString("SudentResult"));

}

rs.close();

        }

 

    }

5.//用pstdao类修改数据库数据

在PstDao类中加入修改方法

public static int update(String sql,Object ...params){

Connection connection=getConnection();

PreparedStatement pst=null;

try{

pst=getConnection().prepareStatement(sql);

for(int i=0; i<params.length;i++){

pst.setObject(i+1,params[i]);

    }

return pst.executeUpdate();

    }catch(SQLException e){

    e.printStackTrace();

}finally{

try{

    if(pst!=null){

    pst.close();

}

if(connection!=null){

connection.close();

    }

}

return -1;

}

在PstDaoTest中调用方法

public void update(){

String sql="update account set cash=cash-? where name=?";

int num=PstDao.update(sql,1000,"ICBC");

System.out.println(num>0? "更新成功":"更新失败")；

}

6.为了使数据可以更改，需要配置文件

在根目录文件夹下面创建 resource包

并将包标记为resouces，在包里面创建一个file文件

定义为db.properties

内容为：driver=com.mysql.jdbc.Driver

url=jdbc:mysql://192.168.183.31:3306/Myschool

user=root

pwd=ok

新建一个Prop类

public class Prop{

private static Properties p=new Properties();

public static String getP(String param){

    try{

    p.load(new FileInputStream("resource/db.properties"));

            } catch(IOException e){

            e.printStackTrace()}  ;

    }

return p.getProperty(param);

}

最后将PstDao修改

private static String driver=Prop.getP("driver");

private static String url=Prop.getP("url");

private static String user=Prop.getP("user");

private static String pwd=Prop.getP("pwd");

再修改getConnection方法

Class.forName(driver）;