db.properties 数据库配置信息
url=jdbc:mysql://localhost:3306/contact_sys
user=root
password=root
driverClass=com.mysql.jdbc.Driver
jdbcUtil.java 连接数据库
package d_util;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class jdbcUtil {
private static String url = null;
private static String user = null;
private static String password = null;
private static String driverClass = null;
/**
* 静态代码块中(只加载一次)
*/
static{
try {
//读取db.properties文件
Properties props = new Properties();
/**
* . 代表java命令运行的目录
* 在java项目下,. java命令的运行目录从项目的根目录开始
* 在web项目下, . java命令的而运行目录从tomcat/bin目录开始
* 所以不能使用点.
*/
//FileInputStream in = new FileInputStream("./src/db.properties");
/**
* 使用类路径的读取方式
* / : 斜杠表示classpath的根目录
* 在java项目下,classpath的根目录从bin目录开始
* 在web项目下,classpath的根目录从WEB-INF/classes目录开始
*/
InputStream in = jdbcUtil.class.getResourceAsStream("/db.properties");
//加载文件
props.load(in);
//读取信息
url = props.getProperty("url");
user = props.getProperty("user");
password = props.getProperty("password");
driverClass = props.getProperty("driverClass");
//注册驱动程序
Class.forName(driverClass);
} catch (Exception e) {
e.printStackTrace();
System.out.println("驱程程序注册出错");
}
}
public static Connection getConnection(){
try {
Connection conn = DriverManager.getConnection(url,user,password);
return conn;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new RuntimeException();
}
}
public static void close(Connection conn,Statement stat,ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(stat!=null)
try {
stat.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
entity Admin.java 实体类
public class Admin {
private int id;
private String userName;
private String passWord;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassWord() {
return passWord;
}
public void setPassWord(String passWord) {
this.passWord = passWord;
}
}
Dao AdminDao.java 数据访问
package c_metadata;
import a_beans.Admin;
public class AdminDao extends BaseDao{
// 删除
public void delete(int id) {
String sql = "delete from admin where id=?";
Object[] paramsValue = {id};
super.update(sql, paramsValue);
}
// 插入
public void save(Admin admin) {
String sql = "insert into admin (userName,pwd) values (?,?)";
Object[] paramsValue = {admin.getUserName(),admin.getPwd()};
super.update(sql, paramsValue);
}
// 查询全部
public List<Admin> getAll(){
String sql = "select * from admin";
List<Admin> list = super.query(sql, null, Admin.class);
return list;
}
// 根据条件查询(主键)
public Admin findById(int id){
String sql = "select * from admin where id=?";
List<Admin> list = super.query(sql, new Object[]{id}, Admin.class);
return (list!=null&&list.size()>0) ? list.get(0) : null;
}
}
Dao BaseDao.java
package c_metadata;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import d_util.jdbcUtil;
/*
* 通用的到,自己写的dao都继承此类
* */
public class BaseDao {
private Connection conn;
private PreparedStatement stat;
private ResultSet rs=null;
/*
* 更新的通用方法
* sql:更新的sql语句(update/insert/delete)
* paramsValue sql语句中占位符对应的值(如果没有占位符,传入null)
* */
public void update(String sql,Object[] paramsValue){
try {
conn = jdbcUtil.getConnection();
stat = conn.prepareStatement(sql);
int count = stat.getParameterMetaData().getParameterCount();
//设置占位符参数的值
if(paramsValue!=null &¶msValue.length>0){
//循环给参数赋值
for(int i=0;i<count;i++){
stat.setObject(i+1, paramsValue[i]);
}
}
stat.executeUpdate();
} catch (Exception e) {
// TODO: handle exception
throw new RuntimeException();
}finally {
jdbcUtil.close(conn, stat,rs);
}
}
}
/*
查询的通用方法
*/
public <T> List<T> query(String sql, Object[] paramsValue,Class<T> clazz){
try {
// 返回的集合
List<T> list = new ArrayList<T>();
// 对象
T t = null;
// 1. 获取连接
con = JdbcUtil.getConnection();
// 2. 创建stmt对象
pstmt = con.prepareStatement(sql);
// 3. 获取占位符参数的个数, 并设置每个参数的值
//int count = pstmt.getParameterMetaData().getParameterCount();
if (paramsValue != null && paramsValue.length > 0) {
for (int i=0; i<paramsValue.length; i++) {
pstmt.setObject(i+1, paramsValue[i]);
}
}
// 4. 执行查询
rs = pstmt.executeQuery();
// 5. 获取结果集元数据
ResultSetMetaData rsmd = rs.getMetaData();
// ---> 获取列的个数
int columnCount = rsmd.getColumnCount();
// 6. 遍历rs
while (rs.next()) {
// 要封装的对象
t = clazz.newInstance();
// 7. 遍历每一行的每一列, 封装数据
for (int i=0; i<columnCount; i++) {
// 获取每一列的列名称
String columnName = rsmd.getColumnName(i + 1);
// 获取每一列的列名称, 对应的值
Object value = rs.getObject(columnName);
// 封装: 设置到t对象的属性中 【BeanUtils组件】
BeanUtils.copyProperty(t, columnName, value);
}
// 把封装完毕的对象,添加到list集合中
list.add(t);
}
return list;
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
JdbcUtil.closeAll(con, pstmt, rs);
}
}
}
test.java 测试
package c_metadata;
import org.junit.Test;
public class AdminDaoTest {
@Test
public void testUpdate(){
AdminDao adminDao = new AdminDao();
adminDao.delete(4);
//adminDao.save(new Admin());
}
}
优化实例的基础
package c_metadata;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import org.junit.Test;
import d_util.jdbcUtil;
public class App {
/*
* 1.数据库元信息
* */
@Test
public void test1() throws SQLException{
//获取连接
Connection conn = jdbcUtil.getConnection();
//获取数据库元数据
DatabaseMetaData metaData = conn.getMetaData();
System.out.println("返回一个String对象,代表数据库的URL:"+metaData.getURL());
System.out.println("返回连接当前数据库管理系统的用户名:"+metaData.getUserName());
System.out.println("返回数据库的产品名称:"+metaData.getDatabaseProductName());
System.out.println("返回数据库的版本号:"+metaData.getDatabaseProductVersion());
System.out.println("返回驱动程序的名称:"+metaData.getDriverName());
System.out.println("返回驱动程序的版本号:"+metaData.getDriverVersion());
System.out.println("返回一个boolean值,指示数据库是否只允许读操作:"+metaData.isReadOnly());
}
@Test
public void test2() throws SQLException{
//获取连接
Connection conn = jdbcUtil.getConnection();
String sql = "select * from admin where userName=? and passWord=?";
Object[] values = {"张三","李四"};
PreparedStatement stat = conn.prepareStatement(sql);
//获取参数元数据
ParameterMetaData p_metaDate = stat.getParameterMetaData();
//获取参数的个数
int count = p_metaDate.getParameterCount();
System.out.println(count);
}
@Test
public void test3() throws SQLException{
//获取连接
Connection conn = jdbcUtil.getConnection();
String sql = "select * from admin";
Object[] values = {"张三","李四"};
PreparedStatement stat = conn.prepareStatement(sql);
ResultSet rs = stat.executeQuery();
//获取结果集元数据,得到列名称
ResultSetMetaData rs_metaDate = rs.getMetaData();
while(rs.next()){
//获取列的个数
int count = rs_metaDate.getColumnCount();
for(int i=0;i<count;i++){
//得到列名称
String columntName = rs_metaDate.getColumnName(i+1);
//获取每一行的每一列的值
Object columntValue = rs.getObject(columntName);
System.out.println(columntName+":"+columntValue);
}
}
}
}