Java基础开发之JDBC操作数据库增删改查，分页查询实例详解

Java基础开发之JDBC操作数据库增删改查，分页查询实例详解

对数据库的操作无非就是增删改查，其中数查询操作最为复杂，所以将查询单独讲解，我这里用的mysql数据库

增删改查操作

分页查询操作

1.查询结果以list返回

2.查询结果以jsonArray返回

3.查询总记录条数

先看一下相关的配置信息

public static final String USER_NAME = "root";

public static final String PWD = "123456789";

public static final String DRIVER = "com.mysql.jdbc.Driver";

public static final String URL = "jdbc:mysql://localhost:3306/web_demon";

/** 分页查询默认每页记录条数 */

public static final int PAGE_SIZE_DEFAULT = 10;

增删改操作

获取数据库连接对象

/**

* 获取数据库连接

* @return 数据库连接对象

*/

public Connection getConnection(Connection conn) {

if(conn == null){

try {

Class.forName(Config.DRIVER);

conn = DriverManager.getConnection(Config.URL, Config.USER_NAME, Config.PWD);

} catch (Exception e) {

e.printStackTrace();

}

}

return conn;

}

封装增删改操作

/**

* 新增，删除，插入操作

* @param sql 执行的sql语句

* 例如：新增语句 "insert into user (name,sex) values (?,?)";

* 删除语句 "delete from user where id=?";

* 修改语句 "update user set name=?,sex=? where id=? and sex=?";

* @param values 对应的参数值

* @return 影响条数，-1为异常

*/

private int execute(String sql,Object... values){

Connection conn = null;

PreparedStatement pStmt = null;

try {

conn = this.getConnection(conn);

pStmt = conn.prepareStatement(sql);

//设置参数

if(pStmt != null && values != null && values.length > 0){

for (int i = 0; i < values.length; i++) {

pStmt.setObject(i+1, values[i]);

}

}

int i =pStmt.executeUpdate();

return i;

} catch (Exception e) {

e.printStackTrace();

} finally {

if (conn != null) {

this.closeConnection(conn);

}

}

return -1;

}

调用方法

//新增

public static void insert(){

String sql = "insert into user (name,sex) values (?,?)";

Object[] values = new Object[]{"李四",0};

int res = baseImp.execute(sql, values);

System.out.println("insert res="+res);

}

//删除

public static void delete(){

String sql = "delete from user where id=?";

Object[] values = new Object[]{2};

int res = baseImp.execute(sql, values);

System.out.println("delete res="+res);

}

//更新

public static void update(){

String sql = "update user set name=?,sex=? where id=? and sex=?";

Object[] values = new Object[]{"张三",1,1,0};

int res = baseImp.execute(sql, values);

System.out.println("update res="+res);

}

查询操作

1.查询结果以list返回

/**

* 查询结果以list返回

* @param pageIndex 页数

* @param pageSize 每页记录条数

* @param attachTableName 在结果集中是否给key值附加表名，例如：user.id,与id

* @param sql 查询语句 例如："select u.id,u.name,u.sex,u.depart_id AS departId,d.* from user u,depart d where u.depart_id=d.id"

* @param values

* @throws SQLException

*/

private List

Connection conn = null;

PreparedStatement pStmt = null;

List

//校验参数

if(pageIndex <= 0){

pageIndex = 1;

}

if(pageSize <= 0){

pageSize = Config.PAGE_SIZE_DEFAULT;

}

conn = this.getConnection(conn);

pStmt = conn.prepareStatement(sql);

//设置参数

if(pStmt != null && values != null && values.length > 0){

for (int i = 0; i < values.length; i++) {

pStmt.setObject(i+1, values[i]);

}

}

//设置最大查询到第几条记录

pStmt.setMaxRows(pageIndex*pageSize);

ResultSet rs = pStmt.executeQuery();

//游标移动到要输出的第一条记录

rs.relative((pageIndex-1)*pageSize);

if(rs != null){

try {

dataList = new ArrayList

ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等

//遍历结果集

while(rs.next()){

Map map = new LinkedHashMap();

for (int i = 1; i <= md.getColumnCount(); i++) {

map.put(attachTableName?(formatTableName(md.getTableName(i))+"."+md.getColumnLabel(i)):(md.getColumnLabel(i)), rs.getObject(i));

}

dataList.add(map);

}

}finally{

if(rs != null){

rs.close();

}

if(pStmt != null){

pStmt.close();

}

if (conn != null) {

this.closeConnection(conn);

}

}

}

return dataList;

}

调用list查询

public static void queryList(){

String sql = "select u.*,d.* from user u,depart d where u.depart_id=d.id";

try {

List

// List

for (Map map : dataList) {

for (String key : map.keySet()) {

System.out.print(key+"="+map.get(key)+" ");

}

System.out.println();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

结果

2.查询结果以jsonArray返回

/**

* 查询结果以ArrayList返回

* @param 在结果集中是否给key值附加表名，例如：user.id,与id

* @param sql 查询语句 例如："select u.id,u.name,u.sex,u.depart_id AS departId,d.* from user u,depart d where u.depart_id=d.id"

* @param values

* @throws SQLException

*/

private JSONArray queryJsonArray(int pageIndex,int pageSize,boolean attachTableName,String sql,Object... values) throws SQLException{

JSONArray jsonArray = null;

Connection conn = null;

PreparedStatement pStmt = null;

//校验参数

if(pageIndex <= 0){

pageIndex = 1;

}

if(pageSize <= 0){

pageSize = Config.PAGE_SIZE_DEFAULT;

}

conn = this.getConnection(conn);

pStmt = conn.prepareStatement(sql);

//设置参数

if(pStmt != null && values != null && values.length > 0){

for (int i = 0; i < values.length; i++) {

pStmt.setObject(i+1, values[i]);

}

}

//设置最大查询到第几条记录

pStmt.setMaxRows(pageIndex*pageSize);

ResultSet rs = pStmt.executeQuery();

//游标移动到要输出的第一条记录

rs.relative((pageIndex-1)*pageSize);

if(rs != null){

try {

jsonArray = new JSONArray();

ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等

//遍历结果集

while(rs.next()){

JSONObject jsonObject = new JSONObject();

for (int i = 1; i <= md.getColumnCount(); i++) {

jsonObject.put(attachTableName?(formatTableName(md.getTableName(i))+"."+md.getColumnLabel(i)):(md.getColumnLabel(i)), rs.getObject(i)+"");

}

jsonArray.add(jsonObject);

}

}finally{

if(rs != null){

rs.close();

}

if(pStmt != null){

pStmt.close();

}

if (conn != null) {

this.closeConnection(conn);

}

}

}

return jsonArray;

}

调用jsonArray查询

public static void queryJsonArray(){

// String sql = "select * from user u";

String sql = "select u.*,d.* from user u,depart d where u.depart_id=d.id";

// String sql = "select u.id AS uid,u.name,u.sex,u.depart_id AS departId,d.name from user u,depart d where u.depart_id=d.id";

// String sql = "select u.id,u.name,u.sex,u.depart_id AS departId,d.* from user u,depart d where u.depart_id=d.id";

try {

JSONArray jsonArray = baseImp.queryForJsonArrayAttachTableName(2,2,sql, null);

// JSONArray jsonArray = baseImp.queryForJsonArray(sql, null);

System.out.println(jsonArray.toString());

for (int i = 0; i < jsonArray.size(); i++) {

JSONObject jsonObject = jsonArray.getJSONObject(i);

Iterator> iterator = jsonObject.keys();

Object key = null;

while (iterator.hasNext()) {

key = iterator.next();

System.out.print(key+" "+jsonObject.get(key)+" ");

}

System.out.println();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

结果

[{"user.id":"4","user.name":"王五","user.sex":"0","user.depart_id":"3","depart.id":"3","depart.name":"研发部","depart.desc":"这是研发部"},{"user.id":"5","user.name":"赵六","user.sex":"1","user.depart_id":"1","depart.id":"1","depart.name":"测试部","depart.desc":"这是测试部"}]

user.id 4 user.name 王五 user.sex 0 user.depart_id 3 depart.id 3 depart.name 研发部 depart.desc 这是研发部

user.id 5 user.name 赵六 user.sex 1 user.depart_id 1 depart.id 1 depart.name 测试部 depart.desc 这是测试部

3.查询总记录条数

/**

* 查询记录条数

* @param sql 例如："select count(*) from user where xxx"

* @param values

* @throws SQLException

*/

public int queryCount(String sql,Object... values) throws SQLException{

int count = -1;

Connection conn = null;

PreparedStatement pStmt = null;

conn = this.getConnection(conn);

pStmt = conn.prepareStatement(sql);

//设置参数

if(pStmt != null && values != null && values.length > 0){

for (int i = 0; i < values.length; i++) {

pStmt.setObject(i+1, values[i]);

}

}

ResultSet rs = pStmt.executeQuery();

if(rs != null){

try {

while(rs.next()){

count = rs.getInt(1);

}

}finally{

if(rs != null){

rs.close();

}

if(pStmt != null){

pStmt.close();

}

if (conn != null) {

this.closeConnection(conn);

http:// }

}

}

return count;

}

调用查询总记录条数

public static void queryCount(){

String sql = "select count(*) from user u";

try {

System.out.println("count="+baseImp.queryCount(sql, null));

} catch (SQLException e) {

e.printStackTrace();

}

}

结果

至此我们介绍完了java基础开发中JDBC对数据库进行增删改查操作与分页查询，如果想了解更多关于这方面的文章大家可以查看下面的相关链接

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。