jdbc设计
数据库驱动。
jdbc编程 ：
//1.加载数据库驱动
//2.获取数据库连接
//3.执行sql语句
//4.释放资源
Class.forName("com.mysql.jdbc.Driver");
java.sql.Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db","root","root");
String sql = "insert student(name) values('lili')";
Statement stat = conn.createStatement();
stat.executeUpdate(sql);//只能执行insert update delete
//ResultSet rs = stat.executeQuery("select * from student");//执行查询
while(rs.next()){
int i = rs.getInt(1);//从1开始range
int id = rs.getInt("id");
}
rs.close();
stat.close();
conn.close();
PreparedStatement
对于项目的新型的理解为：能进行抽象出来进行单独处理的就尽可能的进行抽离。配置文件，项目结构，单独的项目等等。
对于独立jdbc最大话的简化就是配置文件，orm中间件。有这两个就可以对数据进行独立的操作。
上述是一个封装操作。
package com.kaishengit.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import com.kaishengit.entity.Person;
import com.kaishengit.util.DbHelp;
import com.kaishengit.util.RowMapper;
public class PersonDao {
private DbHelp help = new DbHelp();
public void save(Person person) {
String sql = "insert into person(username,tel,email) values(?,?,?)";
help.executeUpdate(sql,person.getUsername(),person.getTel(),person.getEmail());
}
public void update(Person person) {
String sql = "update person set tel = ?,email = ?,username=? where id = ?";
help.executeUpdate(sql, person.getTel(),person.getEmail(),person.getUsername(),person.getId());
}
public void delete(int id) {
String sql = "delete from person where id = ?";
help.executeUpdate(sql, id);
}
public Person findById(int id) {
String sql = "select * from person where id = ?";
return (Person)help.queryForObject(sql,new PersonRowMapper(),id);
}
public List<Person> findAll() {
String sql = "select * from person";
return help.queryForList(sql, new PersonRowMapper());
}
public Person findByName(String name) {
String sql = "select id,username from person where username = ?";
return (Person) help.queryForObject(sql, new RowMapper(){
@Override
public Object mapperRow(ResultSet rs) throws SQLException {
Person p = new Person();
p.setId(rs.getInt("id"));
p.setUsername(rs.getString("username"));
return p;
}
}, name);
}
private class PersonRowMapper implements RowMapper{
@Override
public Object mapperRow(ResultSet rs) throws SQLException {
Person p = new Person();
p.setEmail(rs.getString("email"));
p.setId(rs.getInt("id"));
p.setTel(rs.getString("tel"));
p.setUsername(rs.getString("username"));
return p;
}
}
}
以上是一个简单的封装。
对于1.抽象中RowMapper的封装。2直接用field也可以使用。所以直接用getClass()中的field 进行封装解析。
内部类：
内部类创建实例：
匿名局部内部类的典型用法：
还有一个典型的匿名局部内部类：
匿名内部实现RowMapper接口。
泛型：
反射：
String className = "com.ajy.entity.Person";
try {
Class<?> clazz = Class.forName(className);
//根据完全限定名创建出对应类的对象
Object obj = clazz.newInstance(); //1.调用的是person类的无参构造
Method[] methods = clazz.getMethods();
for(Method m : methods) {
System.out.println(m.getName());
}
Field[] fields = clazz.getFields();//2.获取是clazz的所有域属性
Method method = clazz.getMethod("sayHello",String.class);
String str = (String)method.invoke(obj,"Jack");
System.out.println(str);
} catch (Exception e) {
e.printStackTrace();
}
2.第二种封装RowMapper方法：
简化版：
public class BeanPropertyRowMapper<T> implements RowMapper<T>{
private Class<T> clazz;
public BeanPropertyRowMapper(Class<T> clazz) {
this.clazz = clazz;
}
@Override
public T mapperRow(ResultSet rs) throws SQLException {
T obj = null;
try {
obj = clazz.newInstance();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
String columnName = rsmd.getColumnLabel(i);
String methodName = "set"+columnName.substring(0,1).toUpperCase()+columnName.substring(1);
Class<?> paramType = null;
int columnType = rsmd.getColumnType(i);
if(Types.INTEGER == columnType) {
paramType = Integer.TYPE;
} else if(Types.VARCHAR == columnType) {
paramType = String.class;
} else if(Types.FLOAT == columnType) {
paramType = Float.class;
}
Method method = clazz.getMethod(methodName, paramType);
method.invoke(obj, rs.getObject(columnName));
}
} catch (Exception e) {
e.printStackTrace();
}
return obj;
}
}
修改之后的优化版本：
public class BeanPropertyRowMapper<T> implements RowMapper<T>{
private Class<T> clazz;
public BeanPropertyRowMapper(Class<T> clazz) {
this.clazz = clazz;
}
@Override
public T mapperRow(ResultSet rs) throws SQLException {
T obj = null;
try {
obj = clazz.newInstance();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
String columnName = rsmd.getColumnLabel(i);
Object columnValue = rs.getObject(columnName);
setPropertyValue(obj,columnName,columnValue);
}
} catch (Exception e) {
e.printStackTrace();
}
return obj;
}
private void setPropertyValue(T obj, String columnName, Object columnValue) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {
String methodName = "set"+columnName.substring(0,1).toUpperCase()+columnName.substring(1);
Method[] methods = clazz.getMethods();
for(Method m : methods) {
if(m.getName().equals(methodName)) {
m.invoke(obj, columnValue);
break;
}
}
}
}
还有一种是对应select查数据时可以生成List<Map<String,Object>>
public class MapRowMapper implements RowMapper<Map<String, Object>>{
@Override
public Map<String, Object> mapperRow(ResultSet rs) throws SQLException {
Map<String, Object> map = new HashMap<String, Object>();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
String columnName = rsmd.getColumnLabel(i);
Object value = rs.getObject(columnName);
map.put(columnName, value);
}
return map;
}
}
---------------
学习jdbc的时候可以最后参考学习一下Apache的commons ,org.apache.commons.dbutils.DbUtils; 这个是DbUtils类。可以进行基础的jdbc操作。看一下：
package com.kaishengit.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
public class DBHelp {
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection("jdbc:mysql:///db_15","root","root");
}
public static void update(String sql,Object...params) {
Connection conn = null;
try {
conn = getConnection();
QueryRunner runner = new QueryRunner();
runner.update(conn, sql, params);
DbUtils.close(conn);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
DbUtils.close(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static <T> T query(String sql,ResultSetHandler<T> rsh,Object...params) {
Connection conn = null;
try {
conn = getConnection();
QueryRunner runner = new QueryRunner();
T t = runner.query(conn, sql, rsh, params);
return t;
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
DbUtils.close(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
}
然后是DAO：
package com.kaishengit.dao;
import java.util.List;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.kaishengit.entity.Person;
import com.kaishengit.util.DBHelp;
public class PersonDao {
public void save(Person person) {
String sql = "insert into person(username,tel,email) values(?,?,?)";
DBHelp.update(sql,person.getUsername(),person.getTel(),person.getEmail());
}
public void delete(int id) {
String sql = "delete from person where id = ?";
DBHelp.update(sql, id);
}
public Person findById(int id) {
String sql = "select * from person where id = ?";
return DBHelp.query(sql, new BeanHandler<Person>(Person.class), id);
}
public List<Person> findAll() {
String sql = "select * from person";
return DBHelp.query(sql, new BeanListHandler<Person>(Person.class));
}
}
---------------告一段落
mina 多线程 -----多看看Apache。apache.org
