前言

在大一下学期的时候学的MySQL,那时候只知道在dos窗中运行,学长给的Navicat都不知道怎么用.dos窗中运行MySQL在外行看来是很np的样子,实际上很累的.看着窗口中出现的SQL语句虽然很有成就感,但是很不直观.之后两个同工作室的大佬告诉我Navicat怎样怎样用的,我才明白原来还有这么神奇的东西.但是总是用左右手操作太不爽了吧(手其实还可以干点别的事情啊)!!!

学长开了几天的MySQL和java连接的课,但是我都没时间,只去了一次,感觉好可惜!!!之后学长让一个大佬教我,我也是迷迷糊糊的学着,只知道这样用,为什么这样用就不得而知了.

随着学的东西越来越多,越来越明白道理都是相通的,现在我看这个MySQL和java的连接就像是java在用流操作文件,因为在电脑中的MySQL表就是一个个的文件.不多说了,

---

开搞

1 安装好MySQL和Navicat,配好jdk和eclipse

2 MySQL5和MySQL8连接的不同

     jar包的版本不同
          MySQL5是5开头的,MySQL8是8开头的
     反射得到的驱动类路径不同
          MySQL5是com.mysql.jdbc.Driver
          MySQL8是com.mysql.cj.jdbc.Driver
     连接用的URL不同
          MySQL5是jdbc:mysql://localhost:3306/库名
          MySQL8是jdbc:mysql://localhost:3306/库名?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=GMT%2B8 (serverTimezone=GMT%2B8: 有时区的差别)
       (至于其他的不同我就没怎么在意了,mysql的jar包自行下载)

/*8的连接方式*/
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/库名?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=GMT%2B8","用户名", "密码");

/*最好是抽成一个类*/
/*例*/
public class Conn {

    static Connection conn;

    public static Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/库名?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=GMT%2B8","用户名", "密码");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

}

3.用预处理的方式进行crud

PreparedStatement继承自Statement,都是接口,PreparedStatement可以使用占位符,并且 SQL 语句可以被预编译并存储在 PreparedStatement 对象中,可以多次执行.最关键的是PreparedStatement可以将SQL语句进行转义防止SQL注入,详情请看java中PreparedStatement和Statement详细讲解,下面就放上一些crud吧

package teacher.code;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import tescher.util.Conn;

public class Ex3 {

    static Connection conn = conn = Conn.getConnection();;
    static String sql = null;
    static PreparedStatement ps = null;
    static ResultSet res = null;

    public static void main(String[] args) {
        // select();
        // insert();
        // delete();
        update();
    }

    private static void update() {
        try {
            sql = "update student set sname = ? ,sex = ? , age = ? ,snumber = ? where sid = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "哦!!更新了");
            ps.setString(2, "妖");
            ps.setLong(3, 23);
            ps.setString(4, "1214523698");
            ps.setLong(5, 4);
            int res = ps.executeUpdate();
            System.out.println(res);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void delete() {
        try {
            sql = "delete from  student where sname = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "1212");
            int eu = ps.executeUpdate();
            System.out.println(eu);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void insert() {
        try {
            sql = "insert into student (sname,sex,classes) values (?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "dvdsvd");
            ps.setString(2, "妖");
            ps.setLong(3, 123456723);
            int eu = ps.executeUpdate();
            System.out.println(eu);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void select() {
        try {
            sql = "select sid,sname,sex,age,classes,snumber from student where sid = ? ";
            ps = conn.prepareStatement(sql);
            ps.setLong(1, 4);
            ResultSet res = ps.executeQuery();
            while (res.next()) {
                String biaoge = res.getInt("sid") + "\t" + res.getString("sname") + "\t" + res.getString("sex") + "\t"
                        + res.getInt("age") + "\t" + res.getInt("classes") + "\t" + res.getString("snumber");
                System.out.println(biaoge);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

4 在用预处理进行crud时候代码太多了,可以用框架dbutils(dbutils的jar包自行下载)

DBUtils封装了JDBC的操作,有三个核心功能
     QueryRunner中提供对sql语句操作的API.
     ResultSetHandler接口，用于定义select操作后，怎样封装结果集.
     定义了关闭资源与事务处理的方法

     QueryRunner核心类：
          QueryRunner(DataSource ds) 传入连接池
          update(Connection conn,String sql, Object… params) 执行insert update delete操作
          query(Connection conn,String sql, ResultSetHandler rsh, Object… params) 执行 select操作
下面放上一些实例

package org.vector.domain;

import java.io.Serializable;

public class Category implements Serializable {
    private String cid;
    private String cname;
    public String getCid() {
        return cid;
    }
    public void setCid(String cid) {
        this.cid = cid;
    }
    public String getCname() {
        return cname;
    }
    public void setCname(String cname) {
        this.cname = cname;
    }
    @Override
    public String toString() {
        return "Category [cid=" + cid + ", cname=" + cname + "]";
    }

}

package org.vector.dao;

import java.sql.SQLException;
import java.util.List;

import org.vector.domain.Category;

public interface CategoryDao {
        /*接口*/
    public List<Category> findAll() throws SQLException ;

    public void add(Category c) throws SQLException ;

    public Category getById(String cid) throws SQLException ;

    public void update(Category c) throws SQLException ;

    public void delete(String cid) throws SQLException ;
}

package org.vector.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.vector.dao.CategoryDao;
import org.vector.domain.Category;
import org.vector.utils.Conn;

public class CategoryDaoImpl implements CategoryDao {

    //查询所有
    @Override
    public List<Category> findAll() throws SQLException {
        Connection conn = Conn.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "select * from category";
        List<Category> list = qr.query(conn, sql,new BeanListHandler<Category>(Category.class));
        return list;
    }

    //添加
    @Override
    public void add(Category c) throws SQLException {
        Connection conn = Conn.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "insert into category values(?,?) ";
        qr.update(conn, sql, c.getCid(),c.getCname());
        
    }

    //通过id获取一个分类
    @Override
    public Category getById(String cid) throws SQLException {
        Connection conn = Conn.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "select * from category where cid = ? limit 1";
        return qr.query(conn, sql, new BeanHandler<Category>(Category.class),cid);
    }

    //更新分类 
    @Override
    public void update(Category c) throws SQLException {
        Connection conn = Conn.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "update category  set cname = ? where cid = ? limit 1";
        qr.update(conn, sql, c.getCname(),c.getCid());
    }

    public void delete(String cid) throws SQLException {
        Connection conn = Conn.getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "delete from category where cid = ?";
        qr.update(conn, sql,cid);
    }

}