java 连接 mysql_普通java连接mysql

前言

在大一下学期的时候学的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 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 findAll() throws SQLException {

Connection conn = Conn.getConnection();

QueryRunner qr = new QueryRunner();

String sql = "select * from category";

List list = qr.query(conn, sql,new BeanListHandler(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.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);

}

}