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）;