myjdbc;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sound.midi.MetaEventListener;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Field;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSetMetaData;
import domain.UserInfo;
public class JdbcUtil {
//表示数据库的用户名
private final String USERNAME = "root"
;
//表示数据库的密码
private final String PASSWORD = "hang796413"
;
//数据库的驱动信息
private final String DRIVER = "com.mysql.jdbc.Driver"
;
//访问数据库的地址
private final String URL = "jdbc:mysql://localhost:3306/gustomer"
;
//定义数据库的链接
private Connection connection;
//定义sqk语句的执行对象
private java.sql.PreparedStatement pstmt;
//定义查询返回的结果集合
private ResultSet resultSet;
public JdbcUtil(){
try {
Class.forName(DRIVER);
System.out.println("注册驱动成功"
);
connection =
(Connection) DriverManager.getConnection(URL,USERNAME,PASSWORD);
System.out.println("链接成功"
);
} catch (Exception e) {
}
}
/*完成对数据库的表的增删改的操作
*
* @param sql
* @param params
* @retuen
* @throws SQLException
* */
public boolean upderbypaerdstaemnet(String sql, List<Object>
params)throws SQLException{
boolean flag =
false;
int result = -1;
//表示当用户执行增删改所影响数据库的行数
pstmt =
connection.prepareStatement(sql);
int index = 1
;
if(params !=
null && !
params.isEmpty()){
for(
int i = 0; i<params.size();i++
){
pstmt.setObject(index++
, params.get(i));
}
}
System.out.println(pstmt.toString());
result =
pstmt.executeUpdate();
System.out.println(result);
flag = result > 0 ?
true :
false;
return flag;
}
/*查询返回单条记录
* */
public Map<String,Object> findSimpleResult(String sql, List<Object>
params)throws SQLException{
Map<String,Object> map =
new HashMap<String,Object>
();
//组合SQL语句
pstmt =
connection.prepareStatement(sql);
int index = 1
;
if(params !=
null && !
params.isEmpty()){
for(
int i = 0; i<params.size();i++
){
pstmt.setObject(index++
, params.get(i));
}
}
System.out.println(pstmt.toString());
resultSet = pstmt.executeQuery();
//返回查询结果
//获取列的信息 -> metaData
ResultSetMetaData metaData =
(ResultSetMetaData) resultSet.getMetaData();
int col_len = metaData.getColumnCount();
//获得列的名称
System.out.println(col_len);
//如果还有其他列
while(resultSet.next()){
for(
int i = 0; i<col_len;i++
){
//记录 名字和值
System.out.println("11112333332233332"
);
//i+1!!!!!!!!
String cols_name = metaData.getColumnName(i+1
);
System.out.println(cols_name);
Object cols_value =
resultSet.getObject(cols_name);
if( cols_value ==
null){
cols_value = " "
;
}
map.put(cols_name, cols_value);
}
}
return map;
}
/*
* 查询返回多行记录
* */
public List<Map<String,Object>> findMoreResult(String sql, List<Object>
params)throws SQLException{
List<Map<String,Object>> list =
new ArrayList<Map<String,Object>>
();
pstmt =
connection.prepareStatement(sql);
int index = 1
;
if(params !=
null && !
params.isEmpty()){
for(
int i = 0; i<params.size();i++
){
pstmt.setObject(index++
, params.get(i));
}
}
System.out.println(pstmt.toString());
resultSet = pstmt.executeQuery();
//返回查询结果
//获取列的信息 -> metaData
ResultSetMetaData metaData =
(ResultSetMetaData) resultSet.getMetaData();
int col_len = metaData.getColumnCount();
//获得列的名称
while(resultSet.next()){
//******多了这个地方****/
Map<String,Object> map =
new HashMap<String,Object>
();
for(
int i = 0; i<col_len;i++
){
//记录 名字和值
String cols_name = metaData.getColumnName(i+1
);
Object cols_value =
resultSet.getObject(cols_name);
if( cols_value ==
null){
cols_value = " "
;
}
map.put(cols_name, cols_value);
}
list.add(map);
}
return list;
}
//jdbc的封装可以使用反射机制来封装
public <T> T findSimpleRefResult(String sql ,List<Object>
params,
Class<T> cls)
throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {
T resultObject =
null;
int index = 1
;
pstmt =
connection.prepareStatement(sql);
if(params !=
null && !
params.isEmpty()){
for(
int i = 0; i<params.size();i++
){
pstmt.setObject(index++
, params.get(i));
}
}
resultSet =
pstmt.executeQuery();
ResultSetMetaData metaData =
(ResultSetMetaData) resultSet.getMetaData();
int cols_len =
metaData.getColumnCount();
while(resultSet.next()){
//通过反射机制创建实例
resultObject =
cls.newInstance();
//初始化
for(
int i = 0; i<cols_len;i++
){
//记录 名字和值
String cols_name = metaData.getColumnName(i+1
);
Object cols_value =
resultSet.getObject(cols_name);
if( cols_value ==
null){
cols_value = " "
;
}
//获取javabean（UserInfo）对应列的属性
java.lang.reflect.Field field =
cls.getDeclaredField(cols_name);
//打开javabea的访问私有权限
field.setAccessible(
true);
//相对应的javabean进行赋值
field.set(resultObject,cols_value);
}
}
return resultObject;
}
/*
* 通过反射机制访问数据库
* */
public <T> List <T> finMoreRefResult(String sql ,List<Object>
params,
Class<T> cls)
throws SQLException, IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException, InstantiationException{
List <T> list =
new ArrayList<T>
();
int index = 1
;
pstmt =
connection.prepareStatement(sql);
if(params !=
null && !
params.isEmpty()){
for(
int i = 0; i<params.size();i++
){
pstmt.setObject(index++
, params.get(i));
}
}
resultSet =
pstmt.executeQuery();
ResultSetMetaData metaData =
(ResultSetMetaData) resultSet.getMetaData();
int cols_len =
metaData.getColumnCount();
while(resultSet.next()){
//通过反射机制创建实例
T resultObject =
cls.newInstance();
//初始化
for(
int i = 0; i<cols_len;i++
){
//记录 名字和值
String cols_name = metaData.getColumnName(i+1
);
Object cols_value =
resultSet.getObject(cols_name);
if( cols_value ==
null){
cols_value = " "
;
}
//获取javabean（UserInfo）对应列的属性
java.lang.reflect.Field field =
cls.getDeclaredField(cols_name);
//打开javabea的访问私有权限
field.setAccessible(
true);
//相对应的javabean进行赋值
field.set(resultObject,cols_value);
}
list.add(resultObject);
}
return list;
}
public void releaseConn(){
if(resultSet !=
null){
try {
resultSet.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pstmt !=
null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection !=
null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String[] args){
JdbcUtil jdbcUtil =
new JdbcUtil();
// String sql = "insert into userinfo(username,pswd) values(?,?)";
// List<Object> params = new ArrayList<Object>();
// params.add("rose");
// params.add("123");
// try {
// boolean flag = jdbcUtil.upderbypaerdstaemnet(sql, params);
// System.out.println(flag);
// } catch (Exception e) {
// }
String sql ="select * from userinfo "
;
//查询单个
//List<Object>params = new ArrayList<Object>();
//params.add(1);//id为1的记录
try {
List<UserInfo> list= jdbcUtil.finMoreRefResult(sql,
null,UserInfo.
class);
System.out.println(list);
} catch (Exception e) {
}finally {
jdbcUtil.releaseConn();
}
}
}
package domain;
import java.io.Serializable;
public class UserInfo implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private int id;
private String username;
private String pswd;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Override
public String toString() {
return "UserInfo [id=" + id + ", username=" + username + ", pswd=" + pswd + "]";
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPswd() {
return pswd;
}
public void setPswd(String pswd) {
this.pswd = pswd;
}
public UserInfo(){
}
}
Mysql,JDBC封装
标签：
