1.完成对数据库的表的增删改的操作
2.查询返回单条记录
3.查询返回多行记录
4.可以使用反射机制来封装,查询单条记录
5.反射机制，查询多条记录
package 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(){
}
}