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).CallableStatementdelimiter

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<String,Object>); 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<String, Object> 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<String, Object> queryById(String tablename, Object id) { Map<String, Object> m = new HashMap<String, Object>(); 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<String, Object> 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<String, Object> 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<Map<String, Object>> query(String tablename, String field) { return query(tablename, field, “1=1”, “”); }

public List<Map<String, Object>> query(String tablename) { return query(tablename, “*”, “1=1”, “”); }

public List<Map<String, Object>> query(String tablename, String field, String where, String order) { List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); 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<String, Object> m = new HashMap<String, Object>(); 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<Map<String, Object>> page(int currpage, String tablename, String where, String order) { return page(currpage, tablename, “*”, where, order); }

public List<Map<String, Object>> page(int currpage, String tablename, String order) { return page(currpage, tablename, “*”, “where 1=1”, order); }

public List<Map<String, Object>> page(int currpage, String tablename) { return page(currpage, tablename, “*”, “where 1=1”, “”); }

public List<Map<String, Object>> page(int currpage, String tablename, String fields, String where, String order) { this.currpage = currpage; List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); 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<String, Object> m = new HashMap<String, Object>(); 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(“<ul class=\”pagination\”>”); 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(“<li><a class=\”prev\” href=\”?p=%d\”>上一页</a></li>”, this.currpage – 1)); } for (int i = start; i <= end; i++) { if (i > this.pagecount) break; if (this.currpage == i) { s.append(String.format(“<li class=\”active\”><a href=\”javascript:void(0)\”>%d</a></li>”, i)); continue; } s.append(String.format(“<li><a href=\”?p=%d\”>%d</a></li>”, i, i)); } if (this.currpage < this.pagecount) { s.append(String.format(“<li><a class=\”next\” href=\”?p=%d\”>下一页</a></li>”, this.currpage + 1)); } s.append(“</ul>”); return s.toString(); }

public String pageinfo() { StringBuilder s = new StringBuilder(); s.append(“<div class=\”page\”>”); 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(“<a class=\”prev\” href=\”?p=%d\”>上一页</a>”, this.currpage – 1)); } for (int i = start; i <= end; i++) { if (i > this.pagecount) break; if (this.currpage == i) { s.append(String.format(“<span>%d</span>”, i)); continue; } s.append(String.format(“<a href=\”?p=%d\”>%d</a>”, i, i)); } if (this.currpage < this.pagecount) { s.append(String.format(“<a class=\”next\” href=\”?p=%d\”>下一页</a>”, this.currpage + 1)); } s.append(“</div>”); return s.toString(); }

}

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/154715.html原文链接：https://javaforall.cn