Java数据库连接jdbc

Java数据库连接jdbc

导入java包
1、根目录，新建一个lib目录（Dire）
2、将jar包放入lib目录下
3、File -> Project Structure（项目结构）
4、Libraries-> + ->java->找到项目的lib目录
5、Apply->OK使用JDBC的步骤如下：
//加载数据库驱动 → 建立数据库连接（Connection） → 创建执行SQL语句的Statement对象 → 处理执行结果（ResultSet） → 释放资源Java加载数据库驱动通常是使用Class类的静态方法forName()，语法格式如下：Class.forName(String driverManager)eg:Class.forName("com.mysql.jdbc.Driver" );

创建statement对象

try {Statement statement = conn.createStatement();
} catch (SQLException e) {e.printStackT\frace();
}

创建数据库

try {String sql1="drop database if exists test";String sql2="create database test";statement.executeUpdate(sql1);//执行sql语句statement.executeUpdate(sql2);
} catch (SQLException e) {e.printStackT\frace();
}

创建表

try {statement.executeUpdate("use test");//选择在哪个数据库中操作String sql = "create table table1(" +"column1 int not null, " +"column2 varchar(255)" +")";statement.executeUpdate(sql);
} catch (SQLException e) {e.printStackT\frace();
}

释放资源

//Jdbc程序运行完后，切记要释放程序在运行过程中创建的那些与数据库进行交互的对象，这些对象通常是 ResultSet ,  Statement 和 Connection 对象。
//特别是Connection对象，它是非常稀有的资源，用完后必须马上释放，如果Connection不能及时、正确的关闭，极易导致系统宕机。
//Connection的使用原则是尽量晚创建，尽量早的释放。
finally {try {if(statement!=null)statement.close();if(conn!=null)conn.close();} catch (SQLException e) {e.printStackT\frace();}
}

对数据库增删改查案例：

StudentDaoImpl.java的内容package com.dx.test07.jdbc;import java.sql.Connection;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class StudentDaoImpl extends  BaseDao implements StudentDao{@Override//查询public void  getStudentList(){try {connection = getConnection();    //获取数据库连接String sql = "select * from `student`";    //sql语句ps = connection.prepareStatement(sql);     //语句对象rs= ps.executeQuery();            //执行sql语句while (rs.next()){                //rs 用于存放返回的数据集合int studentNo =rs.getInt( "studentNo");String loginPwd =rs.getString( "LoginPwd");String studentName =rs.getString(  "StudentName");String studentemail =rs.getString("Email");Date date = rs.getDate("BornDate");System.out.println(studentNo+"\t\t"+loginPwd+"\t\t"+studentName +"\t\t"+ studentemail+"\t\t"+date);}}catch (Exception e){e.printStackTrace();}finally {close(connection,ps,rs);}}//String类型转换Timestamp时间戳public Timestamp convertTime(String dateString){DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");try {Date date = dateFormat.parse(dateString);return new Timestamp(date.getTime());} catch (ParseException e) {e.printStackTrace();return null;}}//增加public void addStudent(Student student) {try{connection = getConnection();String sql = "INSERT INTO student values(null,?,?,?,?,?,?,?,?,?)";ps= connection.prepareStatement(sql);ps.setString(1, student.getLoginPwd());ps.setString(2, student.getStudentName());ps.setInt(3, student.getSex());ps.setInt(4, student.getGradeId());ps.setString(5, student.getPhone());ps.setString(6, student.getAddress());ps.setTimestamp(7,convertTime("2024-09-25 09:08:01"));ps.setString(8,student.getEmail());ps.setString(9,student.getIdentityCard());int count = ps.executeUpdate();if(count > 0){System.out.println("添加成功！！");}else System.out.println("添加失败！！");}catch (Exception e){e.printStackTrace();}finally {close(connection,ps,rs);}}//删除public void deleteStudent(int Stu_Num){connection = getConnection();String sql = "DELETE FROM `student` WHERE `StudentNo`= ? ;";try {ps= connection.prepareStatement(sql);ps.setInt(1,Stu_Num);int count = ps.executeUpdate();if(count > 0){System.out.println("添加成功！！");}else System.out.println("添加失败！！");}catch (Exception e){e.printStackTrace();}finally {close(connection,ps,rs);}}//更改public void updataStudent(int StudentNo, String LoginPwd, String StudentName, int Sex, int GradeId, String Phone, String Address, String BornDate, String Email, String IdentityCard){connection = getConnection();String sql = "UPDATE `student` SET `LoginPwd`=?,`StudentName`=?,`Sex`=?,`GradeId`=?,`Phone`=?,`Address`=?,`BornDate`=?,`Email`=?,`IdentityCard`=? WHERE `StudentNo`= ?";try {ps= connection.prepareStatement(sql);ps.setString(1,LoginPwd);ps.setString(2,StudentName);ps.setInt(3,Sex);ps.setInt(4,GradeId);ps.setString(5,Phone);ps.setString(6,Address);ps.setTimestamp(7,convertTime(BornDate));ps.setString(8,Email);ps.setString(9,IdentityCard);ps.setInt(10,StudentNo);int count = ps.executeUpdate();if(count > 0){System.out.println("添加成功！！");}else System.out.println("添加失败！！");}catch (Exception e){e.printStackTrace();}finally {close(connection,ps,rs);}}public static void main(String[] args) {//查询new StudentDaoImpl().getStudentList();System.out.println("----------------------------------");//增加Student stu = new Student();stu.setLoginPwd("123456");stu.setStudentName("武大郎");stu.setSex(1);stu.setGradeId(3);stu.setPhone("15099567049");stu.setAddress("天山区天山路");stu.setEmail("1850534869@qq.com");stu.setIdentityCard("15156156156161511717818");new  StudentDaoImpl().addStudent(stu);//删除new StudentDaoImpl().deleteStudent(1019);//更改邮箱
//int StudentNo
//String LoginPwd
//String StudentName
//int Sex
//int GradeId
//String Phone
//String Address
//String BornDate
//String Email
//String IdentityCardnew StudentDaoImpl().updataStudent(1010,"123123","郭胧演",1,3,"15099555555","新疆乌鲁木齐","2001-10-01 09:10:16","1850534869@qq.com","411422200102010736");System.out.println("----------------------------------");new StudentDaoImpl().getStudentList();}
}

实现一个名为StudentDao.java的接口package com.dx.test07.jdbc;import java.util.Date;public interface StudentDao {/*** 获取学生的数据* */public void getStudentList();public void addStudent(Student student);public void deleteStudent(int studentNO);public void updataStudent(int StudentNo,String LoginPwd,String StudentName,int Sex,int GradeId,String Phone,String Address,String BornDate,String Email,String IdentityCard);
}
//int StudentNo
//String LoginPwd
//String StudentName
//int Sex
//int GradeId
//String Phone
//String Address
//Date BornDate
//String Email
//String IdentityCard

Student.java类的定义
package com.dx.test07.jdbc;import java.util.Date;public class Student {//在数据库中若为varchar类型，在java中写成String类型;private int StudentNo;private String LoginPwd;private String StudentName;private int Sex;private int GradeId;private String Phone;private String Address;private Date BornDate;private String Email;private String IdentityCard;//声明get set方法public int getStudentNo() {return StudentNo;}public void setStudentNo(int studentNo) {StudentNo = studentNo;}public String getLoginPwd() {return LoginPwd;}public void setLoginPwd(String loginPwd) {LoginPwd = loginPwd;}public String getStudentName() {return StudentName;}public void setStudentName(String studentName) {StudentName = studentName;}public int getSex() {return Sex;}public void setSex(int sex) {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 Date getBornDate() {return BornDate;}public void setBornDate(Date 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;}}

BaseDao.java 用来提供数据库连接和返回的
package com.dx.test07.jdbc;
import java.sql.*;public class BaseDao {//数据库连接对象public Connection connection;//创建执行语句的public PreparedStatement ps;//获取结果集合public ResultSet rs;//获得数据库连接public Connection getConnection(){String driver = "com.mysql.cj.jdbc.Driver";// 驱动类String url = "jdbc:mysql://localhost:3306/myschool_db";  //此处是随机命名的一个数据库String name = "root";String passwd = "root";加载驱动类try{//Java加载数据库驱动通常是使用Class类的静态方法forName()Class.forName(driver);//获得数据库连接并返回System.out.println("连接成功！！");return DriverManager.getConnection(url,name,passwd);}catch (Exception e){e.printStackTrace();System.out.println("连接失败！！");return null;}}public void close(Connection connection,PreparedStatement ps, ResultSet rs){try {if(rs!=null) rs.close();if(ps!=null) ps.close();if(connection!=null) connection.close();}catch (Exception e){e.printStackTrace();}}public static void main(String[] args) {//测试连接new BaseDao().getConnection();}
}