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 IdentityCard
        new 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();
    }
}

数据库参考如下