mysql dbutil_Dbutil封装类实现对MySQL数据库的增删改查及分页

packagecom;importjava.io.IOException;importjava.io.InputStream;import java.sql.*;import java.util.*;public classNewdbutil {privateString driver;privateString url;privateString username;privateString password;privateString dbname;privateConnection conn;private int recordcount; //总记录条数

private int currpage = 1;//当前页

private int pagesize = 10;//每页记录条数

private int pagecount = 1;//总页数

/*** 初始化方法，完成配置文件的读取(driver，URL，username，password)*/

public voidinit() {

Properties prop= newProperties();

InputStream is=this.getclass().getClassLoader().getResourceAsStream("db.properties");try{

prop.load(is);this.driver = prop.getProperty("db.driver");this.url = prop.getProperty("db.url");this.username = prop.getProperty("db.username");this.password = prop.getProperty("db.password");

is.close();

}catch(IOException e) {

e.printStackTrace();

}

}/*** 无参构造方法链接数据库中的test库*/

publicNewdbutil() {this.init();try{

Class.forName(this.driver);try{this.conn = DriverManager.getConnection(this.url, this.username, this.password);

}catch(SQLException e) {

e.printStackTrace();

}

}catch(ClassNotFoundException e) {

e.printStackTrace();

}

}/***

功能：实现数据插入操作

*

*@paramsql 数据库预处理语句，实现插入数据功能

*@paramobjs 根据预处理语句填写对应内容，有多少个问号就填几个

*@return

*/

public intsave(String sql, Object... objs) {int result = 0;try{

PreparedStatement pst= this.conn.prepareStatement(sql);for (int i = 0; i < objs.length; i++) {

pst.setObject(i+ 1, objs[i]);

}

result=pst.executeUpdate();

pst.close();

}catch(SQLException e) {

e.printStackTrace();

}returnresult;

}/***

功能：实现数据插入操作

*

*@paramsql 数据库预处理语句，实现插入数据功能

*@paramvalues 根据预处理语句填写对应内容，有多少个问号数组中就填几个值

*@return

*/

public intadd(String sql, Object[] values) {int result = 0;try{

PreparedStatement pst= this.conn.prepareStatement(sql);int indes = 1;for(Object obj : values) {

pst.setObject(indes++, obj);

}

result=pst.executeUpdate();

pst.close();

}catch(SQLException e) {

e.printStackTrace();

}returnresult;

}/***@paramtablename 表名

*@paramvalues map集合，key为表的列名，values为添加的值。

*@return

*/

public int insert(String tablename, Mapvalues) {int result = 0;

StringBuilder f= newStringBuilder(values.size());

StringBuilder v= newStringBuilder(values.size());

Set kset =values.keySet();for(String key : kset) {

f.append(key+ ",");

v.append("?,");

}

String sql= String.format("insert into %s(%s) values(%s)", tablename, f.substring(0, f.length() - 1), v.substring(0, v.length() - 1));try{

PreparedStatement pst= this.conn.prepareStatement(sql);int index = 1;for(String key : kset) {

pst.setObject(index++, values.get(key));

}

result=pst.executeUpdate();

pst.close();

}catch(SQLException e) {

e.printStackTrace();

}returnresult;

}/*** 功能：返回一个表的主键名

*

*@paramtablename

*@return

*/

publicString 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();

}returnpk;

}/*** 功能：根据主键删除一条记录

*

*@paramtablename 表名

*@paramprimarykey 要删除记录的主键名

*@return

*/

public intdelete(String tablename, Object primarykey) {int result = 0;

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

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

pst.setObject(1, primarykey);

result=pst.executeUpdate();

pst.close();

}catch(SQLException e) {

e.printStackTrace();

}returnresult;

}/***@paramtablename

*@paramwhere 表示删除条件一个条件一个问号

*@paramobjs 根据预处理语句填写对应内容，有多少个问号就填几个

*@return

*/

public intdelete(String tablename, String where, Object... objs) {int result = 0;

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

PreparedStatement pst= this.conn.prepareStatement(sql);for (int i = 0; i < objs.length; i++) {

pst.setObject(i+ 1, objs[i]);

}

result=pst.executeUpdate();

pst.close();

}catch(SQLException e) {

e.printStackTrace();

}returnresult;

}/***@paramsql update tablename set name = ? ... where id = ?,

*@paramobjs "andy",...,100 根据update语句填写共有几个值

*@return

*/

public intupdate(String sql, Object... objs) {int result = 0;try{

PreparedStatement pst= this.conn.prepareStatement(sql);for (int i = 0; i < objs.length; i++) {

pst.setObject(i+ 1, objs[i]);

}

}catch(SQLException e) {

e.printStackTrace();

}returnresult;

}/***@paramtablename 表名

*@paramm map集合存放要修改的列名及值

*@paramwhere 修改的条件

*@return

*/

public int update(String tablename, Mapm, String where) {int result = 0;

StringBuilder s= newStringBuilder();for(String k : m.keySet()) {

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

}

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

PreparedStatement pst= this.conn.prepareStatement(sql);int i = 0;for(Object o : m.values()) {

pst.setObject(++i, o);

}

result=pst.executeUpdate();

}catch(SQLException e) {

e.printStackTrace();

}returnresult;

}/*** 功能实现修改数据

*

*@paramtablename 表名

*@parammap map集合存放要修改的列名及值，和表的主键名和主键值

*@return

*/

public int update(String tablename, Mapmap) {int result = 0;

String pk=getPk(tablename);if(map.containsKey(pk)) {

StringBuilder f= newStringBuilder(map.size());

Object pkname=map.get(pk);

map.remove(pk);

Set kset =map.keySet();for(String key : kset) {

f.append(key+ "=?,");

}

String sql= String.format("update %s set %s where %s=?", tablename, f.substring(0, f.length() - 1), pk);try{

PreparedStatement pst= this.conn.prepareStatement(sql);int index = 1;for(String key : kset) {

pst.setObject(index++, map.get(key));

}

pst.setObject(index, pkname);

result=pst.executeUpdate();

pst.close();

}catch(SQLException e) {

e.printStackTrace();

}

}returnresult;

}/*** 功能：实现表的查询工作 只能查询一条结果

*

*@paramsql sql查询语句// select score name from stu where id = ?,1

*@paramparams 条件

*@return

*/

public Mapqueryone(String sql, Object... params) {

Map map = null;try{

PreparedStatement pst= this.conn.prepareStatement(sql);for (int i = 0; i < params.length; i++) {

pst.setObject(i+ 1, params[i]);

}

ResultSet rs=pst.executeQuery();

ResultSetMetaData rsmd=rs.getMetaData();

String[] keys= newString[rsmd.getColumnCount()];for (int c = 1; c <= rsmd.getColumnCount(); c++) {

keys[c- 1] =rsmd.getColumnLabel(c);

}

map= new HashMap();

rs.next();for (int n = 0; n < keys.length; n++) {

map.put(keys[n], rs.getObject(keys[n]));

}

}catch(SQLException e) {

e.printStackTrace();

}returnmap;

}/*** 功能：实现表的查询工作 能查询多条结果,返回List>集合。一条结果就是一个map。

*

*@paramsql sql查询语句// select score name from stu where id = ?,1

*@paramobjs 条件

*@return

*/

public List>query(String sql, Object... objs) {

List> list = null;try{

PreparedStatement pst= this.conn.prepareStatement(sql);for (int i = 0; i < objs.length; i++) {

pst.setObject(i+ 1, objs[i]);

}

ResultSet rs=pst.executeQuery();

ResultSetMetaData rsmd=rs.getMetaData();

String[] keys= newString[rsmd.getColumnCount()];for (int m = 1; m <= rsmd.getColumnCount(); m++) {

keys[m- 1] =rsmd.getColumnLabel(m);

}

list= new ArrayList>();while(rs.next()) {

Map map = new HashMap();for (int n = 0; n < keys.length; n++) {

map.put(keys[n], rs.getObject(keys[n]));

}

list.add(map);

}

}catch(SQLException e) {

e.printStackTrace();

}returnlist;

}/**实现分页查询与多条查询类似

*@paramsql select * from stu where id>?,1

*@paramobjs 条件

*@return

*/

public List>page(String sql, Object... objs) {

List> list = null;

String ccc= "select count(*)" + sql.substring(sql.indexOf("from"));try{

PreparedStatement pst= this.conn.prepareStatement(ccc);int index = 1;for(Object o : objs) {

pst.setObject(index++, o);

}

ResultSet rs=pst.executeQuery();

rs.next();this.recordcount = rs.getInt(1);//总记录条数

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 > getPagecount()) this.currpage = this.pagecount;

String psql= sql + " limit ?,?";

pst= this.conn.prepareStatement(psql);

index= 1;for(Object o : objs) {

pst.setObject(index++, o);

}

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

pst.setInt(index,this.pagesize);

rs=pst.executeQuery();

ResultSetMetaData rsmd=rs.getMetaData();

list= new ArrayList>();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();

}returnlist;

}/*** 实现分页查询功能

*@paramcurrpage 当前页(查询的第几页)

*@parampagesize 每页的记录条数

*@paramtablename 表名

*@paramfields 查询的对象(列名)

*@paramwhere 查询的条件

*@return

*/

public List> page(int currpage, intpagesize, String tablename, String fields, String where) {this.currpage =currpage;this.pagesize =pagesize;

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

}public intgetRecordcount() {returnrecordcount;

}public void setRecordcount(intrecordcount) {this.recordcount =recordcount;

}public intgetCurrpage() {returncurrpage;

}public void setCurrpage(intcurrpage) {this.currpage =currpage;

}public intgetPagesize() {returnpagesize;

}public void setPagesize(intpagesize) {this.pagesize =pagesize;

}public intgetPagecount() {returnpagecount;

}public void setPagecount(intpagecount) {this.pagecount =pagecount;

}publicString getDriver() {returndriver;

}public voidsetDriver(String driver) {this.driver =driver;

}publicString getUrl() {returnurl;

}public voidsetUrl(String url) {this.url =url;

}publicString getUsername() {returnusername;

}public voidsetUsername(String username) {this.username =username;

}publicString getPassword() {returnpassword;

}public voidsetPassword(String password) {this.password =password;

}publicString getDbname() {returndbname;

}public voidsetDbname(String dbname) {this.dbname =dbname;

}publicConnection getConn() {returnconn;

}public voidsetConn(Connection conn) {this.conn =conn;

}

}