mysql jdbc文档_mysql之jdbc

JDBC java数据库连接 用来操纵mysql数据库服务器的一套api接口。

大部分是接口。

java jdbc 各种关系数据库

mysql oracle sqlserver db2

jdbc操作mysql步骤

1)下载mysql jdbc驱动jar文件包。 mysql-xxxx.jar

mysql-connector-java-8.0.20.zip

2) 解压mysql-connector-java-8.0.20.zip 找到 mysql-connector-java-8.0.20.jar

3) 建立java项目，添加mysql-connector-java-8.0.20.jar依赖

4) 编写测试代码

package org.beiyou;

import java.sql.*;

public class Test {

public static void main(String[] args) throws ClassNotFoundException, SQLException {

String driver = "com.mysql.cj.jdbc.Driver";

String url = "jdbc:mysql://localhost:33068/db?serverTimezone=PRC&useUnicode=true&characterEncoding=utf8";

String username = "root";

String password = "root";

//加载驱动

Class.forName("com.mysql.cj.jdbc.Driver");

//建立数据库连接

Connection conn = DriverManager.getConnection(url,username,password);

PreparedStatement pst = conn.prepareStatement("select id,name,address from stu");

ResultSet rs = pst.executeQuery();

while(rs.next()){

String v = rs.getString(3);

System.out.println(v == null ? "地址未填写" : v);

}

conn.close();

}

public static void one(String[] args) throws SQLException {

Connection conn = DriverManager.getConnection("jdbc:mysql:/db?user=root&serverTimezone=PRC");

//System.out.println(conn);

//conn.createStatement().execute("create table aa(a int)");

//conn.createStatement().execute("drop table if exists a1,a2,a3,a4,a5,a6,aa");

//PreparedStatement ps = conn.prepareStatement("show tables");

PreparedStatement ps = conn.prepareStatement("show full tables from `db` where table_type = 'BASE TABLE'");

ResultSet rs = ps.executeQuery();

while(rs.next()){

System.out.println(rs.getString(1));

}

rs.close();

ps.close();

}

}

//加载驱动

Class.forName("com.mysql.cj.jdbc.Driver"); mysql8 serveTimezone=PRC

Class.forname("com.mysql.jdbc.Driver"); mysql5.6 5.5 5.1 user= password= useUnicode=true&characterEncoding=utf8

//建立数据库连接

Connection conn = DriverManager.getConnection(url,username,password);

java.sql.*;

DriverManager

1)Statement 语句对象

Connection conn = new DbUtil().getConn();

//Statement

try {

Statement s = conn.createStatement();

//s.execute() create drop grant revoke

//s.executeQuery() show select

//s.executeUpdate() insert delete update

} catch (SQLException throwables) {

throwables.printStackTrace();

}

}

java jdbc mysql 插入数据时，返回自增id值

select max(id) from users ;

2)PreparedStatement

3).CallableStatement

delimiter $$

create procedure booknewadd(id int,in bn varchar(30),out s int,inout n int)

begin

insert into booknew values(id,bn);

select count(*) into s from booknew;

set n = n * n;

end$$

delimiter ;

CallableStatement cs = conn.prepareCall("{call booknewadd(?,?,?,?)}");

cs.setInt(1,2);

cs.setString(2,"《mysql数据库技术》");

cs.registerOutParameter(3, Types.INTEGER);

cs.setInt(4,11);

cs.registerOutParameter(4, Types.INTEGER);

int i = cs.executeUpdate();

System.out.println(cs.getInt(3));

System.out.println(cs.getInt(4));

System.out.println(i);

编写DbUtil.java类， 工具类

DbUtil du = new DbUtil();

du.add(String sql,HashMap);

du.add(String sql,Object...objs);

du.execute(String sql,int id);

package com.fz.util;

import java.sql.*;

import java.util.*;

/**

* Created by webrx on 2017-08-16.

*/

public class DbUtil {

private String driver = "com.mysql.jdbc.Driver"; // 数据库驱动类

private String url = "jdbc:mysql://localhost:3306/oadb?useUnicode=true&characterEncoding=utf8&useSSL=true";// url

private String uid = "root"; // 账号

private String pwd = "123";// 密码

private int port = 3306;

private Connection conn = null;

private int currpage = 1;

private int pagesize = 5;

private int recordcount = 0;

private int pagecount = 0;

private String dbname = "oadb";

private String host = "localhost";

public void connect(String host, String user, String password, int port, String dbname) {

this.host = host;

this.url = url;

this.uid = user;

this.pwd = password;

this.dbname = dbname;

this.port = port;

this.url = String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf8&useSSL=true",this.host,this.port,this.dbname);

try {

Class.forName(driver);

this.conn = DriverManager.getConnection(url, uid, pwd);

} catch (SQLException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

}

}

public String getPk(String tablename) {

String pk = null;

DatabaseMetaData dbmd;

try {

dbmd = this.conn.getMetaData();

ResultSet rs = dbmd.getPrimaryKeys(this.dbname, null, tablename);

if (rs.next()) {

pk = rs.getString(4);

}

} catch (SQLException e) {

e.printStackTrace();

}

return pk;

}

public int add(String sql, Object[] values) {

int num = 0;

PreparedStatement pst;

try {

pst = this.conn.prepareStatement(sql);

int i = 0;

for (Object o : values) {

pst.setObject(++i, o);

}

num = pst.executeUpdate();

pst.close();

} catch (SQLException e) {

e.printStackTrace();

}

return num;

}

public int insert(String tablename, Map m) {

int num = 0;

StringBuilder n = new StringBuilder();

StringBuilder v = new StringBuilder();

for (String k : m.keySet()) {

v.append("?,");

n.append(k + ",");

}

String sql = String.format("insert into %s(%s) values(%s)", tablename, n.toString().subSequence(0, n.length() - 1), v.toString().subSequence(0, v.length() - 1));

PreparedStatement pst;

try {

pst = this.conn.prepareStatement(sql);

int i = 0;

for (Object o : m.values()) {

pst.setObject(++i, o);

}

num = pst.executeUpdate();

pst.close();

} catch (SQLException e) {

e.printStackTrace();

}

return num;

}

public int deleteById(String tablename, Object id) {

int num = delete(tablename, this.getPk(tablename) + "=" + id);

return num;

}

public int delete(String tablename, String where) {

int num = 0;

String sql = String.format("delete from %s where %s", tablename, where);

try {

PreparedStatement pst = this.conn.prepareStatement(sql);

num = pst.executeUpdate();

pst.close();

} catch (SQLException e) {

e.printStackTrace();

}

return num;

}

public int delete(String tablename) {

int num = delete(tablename, "1=1");

return num;

}

public Map queryById(String tablename, Object id) {

Map m = new HashMap();

String sql = String.format("select * from %s where %s", tablename, this.getPk(tablename) + "='" + id+"'");

try {

PreparedStatement pst = this.conn.prepareStatement(sql);

ResultSet rs = pst.executeQuery();

if (rs.next()) {

ResultSetMetaData rsmd = rs.getMetaData();

int cc = rsmd.getColumnCount();

for (int i = 1; i <= cc; i++) {

String name = rsmd.getColumnLabel(i);

m.put(name, rs.getObject(name));

}

}

} catch (SQLException e) {

e.printStackTrace();

}

return m;

}

public int update(String tablename, Map m) {

int num = 0;

String pk = this.getPk(tablename);

if (m.containsKey(pk)) {

num = update(tablename, m, pk + "='" + m.get(pk)+"'");

} else {

num = update(tablename, m, "1=1");

}

return num;

}

public int update(String tablename, Map m, String where) {

int num = 0;

StringBuilder s = new StringBuilder();

for (String k : m.keySet()) {

s.append(k + "=?,");

}

String sql = String.format("update %s set %s where %s", tablename, s.toString().subSequence(0, s.length() - 1), where);

PreparedStatement pst;

try {

pst = this.conn.prepareStatement(sql);

int i = 0;

for (Object o : m.values()) {

pst.setObject(++i, o);

}

num = pst.executeUpdate();

pst.close();

} catch (SQLException e) {

e.printStackTrace();

}

return num;

}

public void close() {

if (this.conn != null) {

try {

this.conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

public int getCurrpage() {

return currpage;

}

public void setCurrpage(int currpage) {

this.currpage = currpage;

}

public int getPagesize() {

return pagesize;

}

public void setPagesize(int pagesize) {

this.pagesize = pagesize;

}

public int getRecordcount() {

return recordcount;

}

public void setRecordcount(int recordcount) {

this.recordcount = recordcount;

}

public int getPagecount() {

return pagecount;

}

public void setPagecount(int pagecount) {

this.pagecount = pagecount;

}

/**

* host localhost

* user root

* pwd 123

* port 3306

*/

public DbUtil() {

try {

Properties pro = new Properties();

pro.load(DbUtil.class.getClassLoader().getResourceAsStream("db.properties"));

this.driver = pro.getProperty("db.driver");

Class.forName(this.driver);

this.url = pro.getProperty("db.url");

this.uid = pro.getProperty("db.user");

this.pwd = pro.getProperty("db.password");

this.conn = DriverManager.getConnection(this.url, this.uid, this.pwd);

} catch (SQLException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

}

}

public DbUtil(String host,String user,String password,String dbname) {

this.host = host;

this.uid = user;

this.pwd = password;

this.url = String.format("jdbc:mysql://%s:3306/%s?useUnicode=true&characterEncoding=utf8&useSSL=true",this.host,this.dbname);

try {

Class.forName(driver);

this.conn = DriverManager.getConnection(url, uid, pwd);

} catch (SQLException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (Exception e) {

e.printStackTrace();

}

}

public Connection getConn() {

return this.conn;

}

public int count(String tablename, String where) {

int num = 0;

String sql = String.format("select count(*) from %s where %s", tablename, where);

try {

PreparedStatement pst = this.conn.prepareStatement(sql);

ResultSet rs = pst.executeQuery();

if (rs.next()) {

num = rs.getInt(1);

}

rs.close();

pst.close();

} catch (SQLException e) {

e.printStackTrace();

}

return num;

}

public List> query(String tablename, String field) {

return query(tablename, field, "1=1", "");

}

public List> query(String tablename) {

return query(tablename, "*", "1=1", "");

}

public List> query(String tablename, String field, String where, String order) {

List> list = new ArrayList>();

String sql = String.format("select %s from %s where %s %s", field, tablename, where, order);

PreparedStatement pst;

try {

pst = this.conn.prepareStatement(sql);

ResultSet rs = pst.executeQuery();

ResultSetMetaData rsmd = rs.getMetaData();

while (rs.next()) {

Map m = new HashMap();

int cc = rsmd.getColumnCount();

for (int i = 1; i <= cc; i++) {

String name = rsmd.getColumnLabel(i);

m.put(name, rs.getObject(name));

}

list.add(m);

}

} catch (SQLException e) {

e.printStackTrace();

}

return list;

}

public List> page(int currpage, String tablename, String where, String order) {

return page(currpage, tablename, "*", where, order);

}

public List> page(int currpage, String tablename, String order) {

return page(currpage, tablename, "*", "where 1=1", order);

}

public List> page(int currpage, String tablename) {

return page(currpage, tablename, "*", "where 1=1", "");

}

public List> page(int currpage, String tablename, String fields, String where, String order) {

this.currpage = currpage;

List> list = new ArrayList>();

String sql = String.format("select %s from %s %s %s limit ?,?", fields, tablename, where, order);

String qqq = String.format("select count(*) c from %s %s", tablename, where);

try {

// 分页信息

PreparedStatement qpst = this.conn.prepareStatement(qqq);

ResultSet qrs = qpst.executeQuery();

if (qrs.next()) {

this.recordcount = qrs.getInt("c");

this.pagecount = this.recordcount % this.pagesize == 0 ? this.recordcount / this.pagesize : this.recordcount / this.pagesize + 1;

}

if (this.currpage < 1)

this.currpage = 1;

if (this.currpage > this.pagecount)

this.currpage = this.pagecount;

// 分页结果信息

PreparedStatement pst = this.conn.prepareStatement(sql);

pst.setInt(1, this.currpage * this.pagesize - this.pagesize);

pst.setInt(2, this.pagesize);

ResultSet rs = pst.executeQuery();

ResultSetMetaData rsmd = rs.getMetaData();

while (rs.next()) {

Map m = new HashMap();

int cc = rsmd.getColumnCount();

for (int i = 1; i <= cc; i++) {

String name = rsmd.getColumnLabel(i);

m.put(name, rs.getObject(name));

}

list.add(m);

}

} catch (SQLException e) {

e.printStackTrace();

}

return list;

}

public String pagebootstrap() {

StringBuilder s = new StringBuilder();

s.append("

• ");

int start = 1;

int end = 10;

if (this.currpage >= 7) {

start = this.currpage - 5;

end = this.currpage + 4;

}

if (this.currpage != 1) {

s.append(String.format("

上一页", this.currpage - 1));

}

for (int i = start; i <= end; i++) {

if (i > this.pagecount)

break;

if (this.currpage == i) {

s.append(String.format("

%d", i));

continue;

}

s.append(String.format("

%d", i, i));

}

if (this.currpage < this.pagecount) {

s.append(String.format("

下一页", this.currpage + 1));

}

s.append("

");

return s.toString();

}

public String pageinfo() {

StringBuilder s = new StringBuilder();

s.append("

");

int start = 1;

int end = 10;

if (this.currpage >= 7) {

start = this.currpage - 5;

end = this.currpage + 4;

}

if (this.currpage != 1) {

s.append(String.format("上一页", this.currpage - 1));

}

for (int i = start; i <= end; i++) {

if (i > this.pagecount)

break;

if (this.currpage == i) {

s.append(String.format("%d", i));

continue;

}

s.append(String.format("%d", i, i));

}

if (this.currpage < this.pagecount) {

s.append(String.format("下一页", this.currpage + 1));

}

s.append("

");

return s.toString();

}

}