手动封装jdbc和dao层,体会其中的优点与不足。
注:本次采用的mysql数据库记得添加数据库的驱动包。
Dbhelper类:对jdbc进行封装,采用单例模式,不用每次都去连接数据库,释放连接,影响性能,对sql语句进行封装,利用列表数组动态添加数据,
不需要一个一个设置,操作时只需传sql 语句和列表数组,前提是?与数组中的参数要一一对应。返回结果封装:对于执行操作,结果只有成功与失败;
对于查询数据会封装进一个List<Map<String,String>>,同时释放结果集,提高系统的性能,方便用户操作。
不足的是,数据库的信息是硬编码,不利于扩展
package com.lzw.db1.db;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 该类是一个单例模式
* jdbc封装类
*/
public class DbHelper {
private static DbHelper dbHelper = null;
private static Connection connection = null;
private static final String DRIVEN = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://127.0.0.1:3306/book";
private static final String USERNAME = "root";
private static final String PASSWORD = "";
private DbHelper() {
if (connection == null) {
getConnection();
}
}
/**
* 获取数据库连接
*/
private void getConnection(){
try {
Class.forName(DRIVEN);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
System.out.println("数据库连接成功!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 获取实例,
* @return
*/
public static DbHelper getInstance() {
if (dbHelper == null) {
dbHelper = new DbHelper();
}
return dbHelper;
}
/**
*
* @param sql sql语句
* @param list 注入数据
* @return
*/
public boolean excute(String sql, List<String> list) {
boolean is = false;
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
if (list != null)
for (int i = 0; i < list.size(); i++) {
try {
preparedStatement.setString(i + 1, list.get(i));
} catch (SQLException e) {
e.printStackTrace();
}
}
try {
int row = preparedStatement.executeUpdate();
if (row > 0) {
is = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return is;
}
/**
*
* @param sql sql语句
* @param list 注入数据
* @return
*/
public List<Map<String, String>> query(String sql, List<String> list) {
List<Map<String, String>> resultlist = null;
ResultSet resultSet = null;
ResultSetMetaData resultSetMetaData = null;
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
if (list != null)
for (int i = 0; i < list.size(); i++) {
preparedStatement.setString(i + 1, list.get(i));
}
resultSet = preparedStatement.executeQuery();
resultSetMetaData = resultSet.getMetaData();
resultlist = new ArrayList<Map<String, String>>();
while (resultSet.next()) {
Map<String, String> map = new HashMap<String, String>();
for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
//列示从1开始的
map.put(resultSetMetaData.getColumnName(i+1), resultSet.getString(i+1));
}
resultlist.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (resultSet != null)
resultSet.close();
if(preparedStatement!=null)
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return resultlist;
}
}
UserDao:user表基本操作的封装
package com.lzw.db1.dao;
import com.lzw.db1.bean.User;
import com.lzw.db1.db.DbHelper;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* Created by Administrator on 2015/12/6.
*/
public class UserDao {
DbHelper dbHelper=null;
public UserDao(){
dbHelper=DbHelper.getInstance();
}
public User find(String id){
String sql = "SELECT * FROM user where id=?";
List<String> list = new ArrayList<String>();
list.add(id);
List<Map<String,String>> listresult=dbHelper.query(sql, list);
if(listresult==null||listresult.size()==0){
return null;
}else {
User user=new User();
user.setId(listresult.get(0).get("id"));
user.setName(listresult.get(0).get("name"));
user.setPassword(listresult.get(0).get("password"));
return user;
}
}
public boolean add(User user){
String sql = "insert into user values(NULL ,?,?,UNIX_TIMESTAMP())";
List<String> list = new ArrayList<String>();
list.add(user.getName());
list.add(user.getPassword());
return dbHelper.excute(sql,list);
}
public boolean update(User user){
if(user==null||user.getId()==null){
return false;
}
String sql = "update user set name=?,password=? where id=?";
List<String> list = new ArrayList<String>();
list.add(user.getName());
list.add(user.getPassword());
list.add(user.getId());
return dbHelper.excute(sql,list);
}
public boolean delete(String id){
String sql = "delete from user where id=?";
List<String> list = new ArrayList<String>();
list.add(id);
return dbHelper.excute(sql,list);
}
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)