JDBC连接Mysql数据库

编写db.properties文件

　　

　　　　#MySQL配置信息

　　　　username=root

　　　　password=123456

　　　　driver=com.mysql.jdbc.Driver

　　　　url=jdbc:mysql://localhost:3306/Demo

　　说明：username是你当时安装数据库时创建的名字，密码也是自己设定的。当然，你还要将mysql-connector-java-5.1.7-bin.jar这个jar包导入你的编译器，否则driver没有，连不上数据库。

下面附上代码并解析

　　　　结构：

　　说明：所有的代码都放在Jdbc包下，Entity是一个实体类，我把数据库的信息刻画成一个类型，Mysql_Dao是一个接口和实现类，用于定义相应的功能，Test是测试这些方法的类，Tools包下是写的一个Dbutil类，用户连接数据库。

Entity包

package Jdbc.Entity;

public class Emp {
    
    //Attributes
    private int id;
    private String name;
    private String job;
    private int sal;
    private int comm;
    public Emp(){}
    
    //constructor
    public Emp(int id,String name,String job,int sal,int comm){
        this.id = id;
        this.name = name;
        this.job = job;
        this.sal = sal;
        this.comm = comm;
    }
    
    //get and set methods
    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getJob() {
        return job;
    }

    public void setJob(String job) {
        this.job = job;
    }

    public int getSal() {
        return sal;
    }

    public void setSal(int sal) {
        this.sal = sal;
    }

    public int getComm() {
        return comm;
    }

    public void setComm(int comm) {
        this.comm = comm;
    }
    
    //toString method
    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", job='" + job + '\'' +
                ", sal=" + sal +
                ", comm=" + comm +
                '}';
    }
}

Mysql_Dao包

 

package Jdbc.Mysql_Dao;

import Jdbc.Entity.Emp;

import java.util.List;

public interface Operator {
    //define some abstract methods
    public void add(Emp emp);
    public void deleteby_id(Integer id);
    public void modify(Emp emp);
    public Emp findby_id(Integer id);
    public int getTotal();
    public List<Emp> findby_page(int size,int page);
}

 

package Jdbc.Mysql_Dao;

import Jdbc.Entity.Emp;
import Jdbc.Tools.Dbutil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

//implement the operator interface
public class Imp_Ope_Dao implements Operator{

    @Override
    //implement the add method
    public void add(Emp emp) {
        Connection conn = null;
        try{
            conn = Dbutil.getConnection();
            String sql = "insert into emp values(?,?,?,?,?)";
            /**
             * Statement与PreparedStatement的区别
             *
             * 1:Statement执行的是静态sql语句
             * 2:Statement有Sql注入漏洞
             * 3:PreparedStatement是Statement的子类
             * 4:PreparedStatement是预编译sql语句对象
             */
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,emp.getId());
            ps.setString(2,emp.getName());
            ps.setString(3,emp.getJob());
            ps.setInt(4,emp.getSal());
            ps.setInt(5,emp.getComm());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            Dbutil.closeConnection(conn);
        }
    }

    @Override
    //implement the deleteby_id method
    public void deleteby_id(Integer id) {
        Connection conn = null;
        try{
            conn = Dbutil.getConnection();
            String sql = "delete from emp where id = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.execute();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            Dbutil.closeConnection(conn);
        }
    }

    @Override
    //implement the modify method
    public void modify(Emp emp) {
        Connection conn = null;
        try{
            conn = Dbutil.getConnection();
            String sql = "update emp set job = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1,emp.getJob());
            ps.execute();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            Dbutil.closeConnection(conn);
        }
    }

    @Override
    //implement the findby_id method
    public Emp findby_id(Integer id) {
        Connection conn = null;
        Emp emp = new Emp();
        try{
            conn = Dbutil.getConnection();
            String sql = "select * from emp where id = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                int num = rs.getInt(1);
                String name = rs.getString(2);
                String job = rs.getString(3);
                int sal = rs.getInt(4);
                int comm = rs.getInt(5);
                emp = new Emp(num,name,job,sal,comm);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            Dbutil.closeConnection(conn);
        }
        return emp;
    }

    @Override
    //implement the getTotal method
    public int getTotal() {
        Connection conn = null;
        int ans = 0;
        try{
            conn = Dbutil.getConnection();
            String sql = "select count(*) from emp";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            rs.next();
            ans = rs.getInt(1);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            Dbutil.closeConnection(conn);
        }
        return ans;
    }

    @Override
    //implement the findby_page method
    public List<Emp> findby_page(int size,int page) {
        Connection conn = null;
        Emp emp = new Emp();
        List<Emp> list = new ArrayList<>();
        try{
            conn = Dbutil.getConnection();
            String sql = "select * from emp limit ?,?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1,(page-1)*size);
            ps.setInt(2,page*size);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String job = rs.getString(3);
                int sal = rs.getInt(4);
                int comm = rs.getInt(5);
                emp = new Emp(id,name,job,sal,comm);
                list.add(emp);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            Dbutil.closeConnection(conn);
        }
        return list;
    }
}

说明下execute，executeQuery，executeUpdate的区别。

　　executeQuery主要用于select语句。
　　executeUpdate主要用于DDL语句，如insert，delete，update语句。
　　execute主要用于返回多个结果集时。

Tools包

package Jdbc.Tools;

import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class Dbutil {
    public static String url;
    public static String username;
    public static String password;
    public static String driver;
    static {
        // 读取数据库的配置文件信息
        try {
            //1:创建Properties类型
            Properties properties = new Properties();
            FileReader fr = new FileReader("db.properties");
            /**
             *
             * 通过当前类的类加载器所获取的流读取配置文件
             * 类加载器获取的流：默认当前路径为src下
             *
             * */
//            InputStream is = 
//                    DBUtil.class.getClassLoader()
//            .getResourceAsStream("db.properties");
            //3：加载流信息，将文件中的数据封装到prop对象上
            properties.load(fr);
            //4：分析prop
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            driver = properties.getProperty("driver");
            //5:加载驱动
            Class.forName(driver);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    //获取数据库连接对象
    public static Connection getConnection(){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(url,username,password);
        }catch (Exception e){
            e.printStackTrace();
        }
        return conn;
    }
    //关闭数据库连接对象
    public static void closeConnection(Connection conn){
        if(conn!=null){
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    //测试数据库是否连接成功
    public static void main(String[] args) {
        System.out.println(getConnection());
    }
}

Test包

运用junit多个测试来测试相应的功能方法。

package Jdbc.Test;

import Jdbc.Entity.Emp;
import Jdbc.Mysql_Dao.Imp_Ope_Dao;
import Jdbc.Mysql_Dao.Operator;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

public class Test_Mysql{
    @Test
    public void test_add(){
        Emp emp = new Emp(112,"kkk","Police",1999,100);
        Operator op = new Imp_Ope_Dao();
        op.add(emp);
    }

    @Test
    public void test_getTotal(){
        Operator op = new Imp_Ope_Dao();
        int count = op.getTotal();
        System.out.println(count);
    }

    @Test
    public void test_deleteby_id(){
        Operator op = new Imp_Ope_Dao();
        op.deleteby_id(103);
    }

    @Test
    public void test_findby_id(){
        Operator op = new Imp_Ope_Dao();
        Emp emp = op.findby_id(104);
        System.out.println(emp);
    }

    @Test
    public void test_findByPage(){
        Operator op = new Imp_Ope_Dao();
        List<Emp>  list= new ArrayList<>();
        list = op.findby_page(2,2);
        for (Emp i:list){
            System.out.println(i);
        }
    }
}