JAVA的JDBC连接数据库以及读取数据库数据

时间:2024-02-15 21:05:00

一、下载数据库JDBC驱动

     1、1:Mysql驱动

        https://dev.mysql.com/downloads/connector/

 

 

 

下载得到的是一个压缩包,解压后到里面寻找文件:mysql-connector-java-8.0.12.jar

这个:mysql-connector-java-8.0.12.jar就是我们要导入到jJAVA项目中。

二、导入数据库驱动包

    这次我是应用Intellje IDEA开发软件,如果应用eclipse直接把包复制到项目中的lib中即可。

或者:

找到你的包所在位置

然后点击Apply按钮后点击OK

结果:

三、代码实现

  1、建包

  

建类:

package com.yangwansheng.test.util;

import java.sql.*;

public class MysqlJdbcUtil {
    private static String driver = "com.mysql.jdbc.Driver";//驱动
    //数据库
    private static String url = "jdbc:mysql://localhost:3306/worksql?useUnicode=true&characterEncoding=UTF-8";
    private static String user = "YangWanSheng";//用户名
    private static String pwd = "Wansheng";//密码
    private static Connection conn = null;//连接对象
    private static Statement stat = null;//执行语句对象

    //打开数据库连接
    public static void open() {
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //打开连接--实例化数据库连接对象
            conn = DriverManager.getConnection(url, user, pwd);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //关闭数据库连接对象,释放内存
    public static void close() {
        try {
            //判断数据库是否处于连接中
            if (conn != null && conn.isClosed()) {
                conn.close();
            }
            if (stat != null) {
                stat.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /***
     * 查询所有数据
     */
    public static ResultSet executeQuery(String sql) {
        try {
            open();
            stat = conn.createStatement();
            return stat.executeQuery(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 条件查询、分页查询
     * @param sql 数据库执行语句
     * @param o 参数
     * @return 反回一个集合命令
     */
    public static ResultSet executeQuery(String sql,Object... o){
        try {
            open();
            PreparedStatement pst=conn.prepareStatement(sql);
            for(int i=0;i<o.length;i++)
                pst.setObject(i+1, o[i]);
            return pst.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    /**执行增、删、改*/
    public static int executUpeate(String sql, Object... obj) {
        try {
            open();
            PreparedStatement pst = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                pst.setObject(i + 1, obj[i]);
            }
            stat = pst;
            return pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }
}

四、jdbcUtil类的调用

  4、1:创建bo实体类

  

package com.yangwansheng.test.bo;

public class StudentCourse {
    private int id;
    private String Sno;
    private String Cno;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getSno() {
        return Sno;
    }

    public void setSno(String sno) {
        Sno = sno;
    }

    public String getCno() {
        return Cno;
    }

    public void setCno(String cno) {
        Cno = cno;
    }

    public StudentCourse(int id, String sno, String cno) {
        this.id = id;
        Sno = sno;
        Cno = cno;
    }
    public StudentCourse(){}

    @Override
    public String toString() {
        return "StudentCourse{" +
                "id=" + id +
                ", Sno=\'" + Sno + \'\\'\' +
                ", Cno=\'" + Cno + \'\\'\' +
                \'}\';
    }
}

 

  4、2:数据库访问层DAO

package com.yangwansheng.test.dao;

import com.yangwansheng.test.bo.StudentCourse;
import com.yangwansheng.test.util.MysqlJdbcUtil;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StudentCourseDao {
    /**查询所有的学生选课表的信息*/
    public List<StudentCourse> getAllStudentCourse(){
        List<StudentCourse> list = new ArrayList<>();
        String sql = "SELECT `StudentCourse`.id, `Course`.Cname, `Student`.Sname FROM `StudentCourse`";
        sql +=" LEFT JOIN `Course` ON `StudentCourse`.Cno = `Course`.Cno ";
        sql +=" LEFT JOIN `Student` ON `StudentCourse`.Sno = `Student`.Sno LIMIT 0,5";
        ResultSet rs = MysqlJdbcUtil.executeQuery(sql);
        try{
            while (rs.next()){
                StudentCourse stucou = new StudentCourse(
                        rs.getInt("id"),rs.getString("Cname"),
                        rs.getString("Sname")
                );
                list.add(stucou);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            MysqlJdbcUtil.close();
        }
        return null;
    }

    /**
     * 分页查询
     * @param state
     * @param end
     * @return
     */
    public List<StudentCourse> getPageStudentCourse(int state, int end){
        List<StudentCourse> list = new ArrayList<>();
        String sql = "SELECT `StudentCourse`.id, `Course`.Cname, `Student`.Sname FROM `StudentCourse`";
        sql +=" LEFT JOIN `Course` ON `StudentCourse`.Cno = `Course`.Cno ";
        sql +=" LEFT JOIN `Student` ON `StudentCourse`.Sno = `Student`.Sno LIMIT ?,?";
        ResultSet rs = MysqlJdbcUtil.executeQuery(sql,state,end);
        try{
            while (rs.next()){
                StudentCourse stucou = new StudentCourse(
                        rs.getInt("id"),rs.getString("Cname"),
                        rs.getString("Sname")
                );
                list.add(stucou);
            }
            return list;
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            MysqlJdbcUtil.close();
        }
        return null;
    }

    //修改
    public int updateSc(int id ,String Cno){
        String sql ="update StudentCourse set Cno = ? where id=? ";
        int i = MysqlJdbcUtil.executUpeate(sql,Cno,id);
        return i;
    }

    /**
     * 添加
     * @return 大于一添加成功
     */
    public int addSc(String sno, String con){
        String sql ="insert into StudentCourse(Sno,Cno) VALUES(?,?);";
        int i = MysqlJdbcUtil.executUpeate(sql,sno,con);
        return i;
    }

    /**
     * 删除
     * @param id 
     * @return 大于1执行成功
     */
    public int delSc(int id){
        String sql ="delete from StudentCourse where id=?;";
        int i = MysqlJdbcUtil.executUpeate(sql,id);
        return i;
    }
}

 

 4、3:创建测试类

package com.yangwansheng.test.test;

import com.yangwansheng.test.bo.StudentCourse;
import com.yangwansheng.test.dao.StudentCourseDao;

public class MysqlTest {
    public static void main(String[] args) {
        StudentCourseDao SCdao = new StudentCourseDao();
        System.out.println(SCdao.getAllStudentCourse());
    }
}

 

结果: