一、JDBC操作数据库方式
1.加载数据库驱动
2.通过驱动管理类创建数据库连接
3.定义sql语句，获取预处理对象PerparedStatement
4.设置sql语句的参数
5.向数据库发送sql语句并执行，获取执行结果
6.如果是查询，遍历查询结果集，封装到实体对象
7.释放资源
Connection connection = null;
PreparedStatement preparedStatement = null;
Result resultSet = null;
try{
//加载数据库驱动
Class.forName("com.mysql.jdbc.Driver");
//通过驱动管理类获取数据库连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/hgh?characterEncoding=utf8","root","abc123");
//定义sql语句
String sql = "select * from user where id = ?";
//获取预处理Statement对象
preparedStatement = connection.prepareStatement(sql);
//设置参数，第一个参数为sql语句中参数的序号(从1开始)，第二个参数为设置的参数值
preparedStatement.setInteger(1,1);
//向数据库发送sql语句并执行，返回结果集
resultSet = preparedStatement.executeQuery();
//遍历查询结果集，封装成实体对象
while(resultSet.next()){
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
//封装user对象
User user = new User();
user.setId(id);
user.setName(name);
System.out.printLn(user);
}
}catch(Exception e){
e.printStackTrace();
} finally{
//释放资源
connection.close();
}二、自定义持久层框架
思路分析：
1.客户端
1)引入自定义持久层框架
2)数据库配置信息--sqlMapConfig.xml
3)sql配置信息--mapper.xml
2.自定义持久层框架
1)加载配置文件--Resources类
2)创建两个容器对象--Configuration和MappedStatement
3)解析配置文件--dom4j
代码实现：
1.客户端：
1)sqlMapConfig.xml
〈configuration〉
<!--数据库连接信息-->
<property name="driverClass" value="com.mysql.jdbc.Driver"></property>
<property name="jdbcUrl" value="jdbc:mysql:///hgh"></property>
<property name="user" value="root"></property>
<property name="password" value="abc123"></property>
<! --引入sql配置信息-->
<mapper resource="userMapper.xml"></mapper>
</configuration>
2)userMapper.xml
<mapper namespace="com.mb.dao.UserMapper">
<select id="findByCondition" paramterType="com.mb.pojo.User"
resultType="com.mb.pojo.User">
select * from user where id = #{id} and username =#{username}
</select>
<select id="findAll" resultType="com.mb.pojo.User">
select * from user
</select>
</mapper>
3)mbTest类
@Test
public void test() throws Exception {
//配置文件
InputStream resourceAsSteam = Resources.getResourceAsSteam(path：
"sqlMapConfig.xml")
//SqlSessionFactory工厂
SqlSessionFactory build = new
SqlSessionFactoryBuilder().build(resourceAsSteam);
SqlSession sqlSession = build.openSession();
User user = new User();
user.setld(l);
user.setUsername("zhangsan");
//代理对象
UserMapper userMapper = sqlSession.getMappper(UserMapper.class);
User userl = userMapper.findByCondition(user);
System.out.println(userl);
}2.自定义持久层框架：
1)Resources类
public static InputStream getResourceAsStream(String path){
InputStream resourceAsStream = Resources.class.getClassLoader()
.getResourceAsStream(path);
return resourceAsStream;
}2.Configuration类
private DataSource dataSource;
private Map<String,MappedStatement> mappedStatementMap = new HashMap<>();
public DataSource getDataSource() {
return dataSource;
}
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
}
public Map<String, MappedStatement> getMappedStatementMap() {
return mappedStatementMap;
}
public void setMappedStatementMap(Map<String, MappedStatement> mappedStatementMap) {
this.mappedStatementMap = mappedStatementMap;
}3.MappedStatement类
private String id;
private String parameterType;
private String resultType;
private String sql;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getParameterType() {
return parameterType;
}
public void setParameterType(String parameterType) {
this.parameterType = parameterType;
}
public String getResultType() {
return resultType;
}
public void setResultType(String resultType) {
this.resultType = resultType;
}
public String getSql() {
return sql;
}
public void setSql(String sql) {
this.sql = sql;
}4.解析sqlMapConfig.xml的工具类XmlConfigBuilder
private Configuration configuration;
public XmlConfigBuilder() {
this.configuration = new Configuration();
}
public Configuration parseConfiguration(InputStream inputStream) throws DocumentException, PropertyVetoException {
//使用dom4j解析xml文件
Document document = new SAXReader().read(inputStream);
Element rootElement = document.getRootElement();
List<Element> list = rootElement.selectNodes("//property");
Properties properties = new Properties();
for (Element element : list) {
String name = element.attributeValue("name");
String value = element.attributeValue("value");
properties.setProperty(name,value);
}
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
comboPooledDataSource.setDriverClass(properties.getProperty("driverClass"));
comboPooledDataSource.setJdbcUrl(properties.getProperty("jdbcUrl"));
comboPooledDataSource.setUser(properties.getProperty("username"));
comboPooledDataSource.setPassword(properties.getProperty("password"));
configuration.setDataSource(comboPooledDataSource);
//解析mapper配置文件
List<Element> list1 = rootElement.selectNodes("//mapper");
for (Element element : list1) {
String resource = element.attributeValue("resource");
InputStream resourceAsStream = Resources.getResourceAsStream(resource);
new XmlMapperBuilder(configuration).parse(resourceAsStream);
}
return configuration;
}5.解析sqlMapConfig.xml的工具类XmlMapperBuilder
private Configuration configuration;
public XmlMapperBuilder(Configuration configuration) {
this.configuration = configuration;
}
public void parse(InputStream resourceAsStream) throws DocumentException {
Document read = new SAXReader().read(resourceAsStream);
Element rootElement = read.getRootElement();
//mapper跟标签的路径
String resource = rootElement.attributeValue("namespace");
//查询标签
List<Element> select = rootElement.selectNodes("//select");
for (Element element : select) {
String id = element.attributeValue("id");
String parameterType = element.attributeValue("parameterType");
String resultType = element.attributeValue("resultType");
String sql = element.getTextTrim();
MappedStatement mappedStatement = new MappedStatement();
mappedStatement.setId(id);
mappedStatement.setParameterType(parameterType);
mappedStatement.setResultType(resultType);
mappedStatement.setSql(sql);
String key = resource + "." + id;
configuration.getMappedStatementMap().put(key,mappedStatement);
}
//添加标签
List<Element> insert = rootElement.selectNodes("//insert");
for (Element element : insert) {
String id = element.attributeValue("id");
String parameterType = element.attributeValue("parameterType");
String sql = element.getTextTrim();
MappedStatement mappedStatement = new MappedStatement();
mappedStatement.setId(id);
mappedStatement.setParameterType(parameterType);
mappedStatement.setSql(sql);
String key = resource + "." + id;
configuration.getMappedStatementMap().put(key,mappedStatement);
}
//修改标签
List<Element> update = rootElement.selectNodes("//update");
for (Element element : update) {
String parameterType = element.attributeValue("parameterType");
String sql = element.getTextTrim();
String id = element.attributeValue("id");
MappedStatement mappedStatement = new MappedStatement();
mappedStatement.setId(id);
mappedStatement.setParameterType(parameterType);
mappedStatement.setSql(sql);
String key = resource + "." + id;
configuration.getMappedStatementMap().put(key,mappedStatement);
}
//删除标签
List<Element> delete = rootElement.selectNodes("//delete");
for (Element element : delete) {
String sql = element.getTextTrim();
String id = element.attributeValue("id");
String parameterType = element.attributeValue("parameterType");
MappedStatement mappedStatement = new MappedStatement();
mappedStatement.setId(id);
mappedStatement.setParameterType(parameterType);
mappedStatement.setSql(sql);
String key = resource + "." + id;
configuration.getMappedStatementMap().put(key,mappedStatement);
}
}6.SqlSessionFactoryBuilder类
public SqlSessionFactory build(InputStream inputStream) throws DocumentException, PropertyVetoException {
//1.解析配置文件
XmlConfigBuilder xmlConfigBuilder = new XmlConfigBuilder();
Configuration configuration = xmlConfigBuilder.parseConfiguration(inputStream);
//2.创建sqlSessionFactory
SqlSessionFactory sqlSessionFactory
= new DefaultSqlSessionFactory(configuration);
return sqlSessionFactory;
}7.DefaultSqlSessionFactory类及它的父接口SqlSessionFactory
public class DefaultSqlSessionFactory implements SqlSessionFactory{
private Configuration configuration;
public DefaultSqlSessionFactory(Configuration configuration) {
this.configuration = configuration;
}
@Override
public SqlSession openSession() {
SqlSession sqlSession = new DefaultSqlSession(configuration);
return sqlSession;
}
}
public interface SqlSessionFactory {
public SqlSession openSession();
}8.DefaultSqlSession类及它的父接口SqlSession
public class DefaultSqlSession implements SqlSession{
private Configuration configuration;
public DefaultSqlSession(Configuration configuration) {
this.configuration = configuration;
}
@Override
public <E> List<E> selectList(String statementId, Object... params) throws Exception {
MappedStatement mappedStatement = configuration.getMappedStatementMap().get(statementId);
SimpleExecutor simpleExecutor = new SimpleExecutor();
List<Object> list = simpleExecutor.query(configuration, mappedStatement, params);
return (List<E>) list;
}
@Override
public <T> T getMapper(Class<?> mapperClass) {
// 使用动态代理为dao层生成代理对象
Object proxyInstance = Proxy.newProxyInstance(DefaultSqlSession.class.getClassLoader(), new Class[]{mapperClass}, new InvocationHandler() {
@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
// 底层实现JDBC代码
// 根据不同情况调用不同方法
// 获取方法名
String methodName = method.getName();
// 获取方法所在类的全限定名
String className = method.getDeclaringClass().getName();
// 组装statementId
String statementId = className + "." + methodName;
// 获取方法的返回值
Type returnType = method.getGenericReturnType();
String typeName = returnType.getTypeName();
// 判断返回值是否进行了参数泛型化
if (returnType instanceof ParameterizedType){
// 调用selectList方法
List<Object> list = selectList(statementId, args);
return list;
}else{
if (typeName.equals("void")){
update(statementId,args);
return null;
}else {
return selectOne(statementId,args);
}
}
}
});
return (T) proxyInstance;
}
@Override
public <T> T selectOne(String statementId, Object... params) throws Exception {
List<Object> list = selectList(statementId, params);
if (list.size() == 1){
return (T) list.get(0);
}else {
throw new RuntimeException("查询结果为空或者返回结果过多");
}
}
@Override
public void update(String statementId, Object... params) throws Exception {
MappedStatement mappedStatement = configuration.getMappedStatementMap().get(statementId);
SimpleExecutor simpleExecutor = new SimpleExecutor();
simpleExecutor.update(configuration,mappedStatement,params);
}
}
public interface SqlSession {
/**
* 查询全部
* @param <E>
* @return
*/
public <E> List<E> selectList(String statementId,Object... params) throws Exception;
/**
* 生成代理对象
* @param mapperClass
* @param <T>
* @return
*/
public <T> T getMapper(Class<?> mapperClass);
/**
* 条件查询
* @param statementId
* @param params
* @param <T>
* @return
* @throws Exception
*/
public <T> T selectOne(String statementId,Object... params) throws Exception;
/**
* 添加、修改、删除
* @param statementId
* @param params
* @throws Exception
*/
public void update(String statementId,Object... params) throws Exception;
}9.SimpleExecutor类及它的父接口Executor
public class SimpleExecutor implements Executor{
public <E> List<E> query(Configuration configuration,
MappedStatement mappedStatement, Object[] params) throws Exception {
// 1.创建数据库链接
DataSource dataSource = configuration.getDataSource();
Connection connection = dataSource.getConnection();
// 2.获取PreparedStatement对象
String sql = mappedStatement.getSql();
// 转换sql语句，将#{}替换为?
BoundSql boundSql = getBoundSql(sql);
PreparedStatement preparedStatement = connection.prepareStatement(boundSql.getSqlText());
// 3.设置参数
// 获取参数类型的全路径
String parameterType = mappedStatement.getParameterType();
Class<?> parameterTypeClass = getClassType(parameterType);
List<ParameterMapping> parameterMappingList = boundSql.getParameterMappingList();
for (int i = 0; i < parameterMappingList.size(); i++) {
ParameterMapping parameterMapping = parameterMappingList.get(i);
String content = parameterMapping.getContent();
Field declaredField = parameterTypeClass.getDeclaredField(content);
declaredField.setAccessible(true);
Object o = declaredField.get(params[0]);
preparedStatement.setObject(i+1,o);
}
// 4.执行sql
ResultSet resultSet = preparedStatement.executeQuery();
String resultType = mappedStatement.getResultType();
Class<?> resultTypeClass = getClassType(resultType);
ArrayList<Object> objects = new ArrayList<>();
// 5.封装返回结果集
while (resultSet.next()){
Object o = resultTypeClass.newInstance();
// 获取元数据
ResultSetMetaData metaData = resultSet.getMetaData();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
// 字段名
String columnName = metaData.getColumnName(i);
// 字段值
Object value = resultSet.getObject(columnName);
// 使用反射或内省
PropertyDescriptor propertyDescriptor = new PropertyDescriptor(columnName, resultTypeClass);
Method writeMethod = propertyDescriptor.getWriteMethod();
writeMethod.invoke(o,value);
}
objects.add(o);
}
return (List<E>) objects;
}
private Class<?> getClassType(String parameterType) throws ClassNotFoundException {
if (parameterType != null){
return Class.forName(parameterType);
}
return null;
}
private BoundSql getBoundSql(String sql) {
ParameterMappingTokenHandler parameterMappingTokenHandler = new ParameterMappingTokenHandler();
String sqlText = new GenericTokenParser("#{",
"}", parameterMappingTokenHandler).parse(sql);
//获取里面的参数列表
List<ParameterMapping> parameterMappings = parameterMappingTokenHandler.getParameterMappings();
BoundSql boundSql = new BoundSql();
boundSql.setSqlText(sqlText);
boundSql.setParameterMappingList(parameterMappings);
return boundSql;
}
public void update(Configuration configuration,
MappedStatement mappedStatement, Object[] params) throws Exception {
// 1.创建数据库链接
DataSource dataSource = configuration.getDataSource();
Connection connection = dataSource.getConnection();
// 2.获取PreparedStatement对象
String sql = mappedStatement.getSql();
// 转换sql语句，将#{}替换为?
BoundSql boundSql = getBoundSql(sql);
PreparedStatement preparedStatement = connection.prepareStatement(boundSql.getSqlText());
// 3.设置参数
// 获取参数类型的全路径
String parameterType = mappedStatement.getParameterType();
Class<?> parameterTypeClass = getClassType(parameterType);
List<ParameterMapping> parameterMappingList = boundSql.getParameterMappingList();
for (int i = 0; i < parameterMappingList.size(); i++) {
ParameterMapping parameterMapping = parameterMappingList.get(i);
String content = parameterMapping.getContent();
Field declaredField = parameterTypeClass.getDeclaredField(content);
declaredField.setAccessible(true);
Object o = declaredField.get(params[0]);
preparedStatement.setObject(i+1,o);
}
// 4.执行sql
int i = preparedStatement.executeUpdate();
}
}
public interface Executor {
/**
* 查询
* @param configuration
* @param mappedStatement
* @param params
* @param <E>
* @return
* @throws Exception
*/
public <E> List<E> query(Configuration configuration,
MappedStatement mappedStatement, Object[] params) throws Exception;
/**
* 添加、修改、删除
* @throws Exception
*/
public void update(Configuration configuration,
MappedStatement mappedStatement, Object[] params) throws Exception;
}10.BoudSql类--解析出来的sql及工具类utils
public class BoundSql {
private String sqlText;
private List<ParameterMapping> parameterMappingList = new ArrayList();
public String getSqlText() {
return sqlText;
}
public void setSqlText(String sqlText) {
this.sqlText = sqlText;
}
public List<ParameterMapping> getParameterMappingList() {
return parameterMappingList;
}
public void setParameterMappingList(List<ParameterMapping> parameterMappingList) {
this.parameterMappingList = parameterMappingList;
}
}
public class GenericTokenParser {
private final String openToken; //开始标记
private final String closeToken; //结束标记
private final TokenHandler handler; //标记处理器
public GenericTokenParser(String openToken, String closeToken, TokenHandler handler) {
this.openToken = openToken;
this.closeToken = closeToken;
this.handler = handler;
}
/**
* 解析${}和#{}
* @param text
* @return
* 该方法主要实现了配置文件、脚本等片段中占位符的解析、处理工作，并返回最终需要的数据。
* 其中，解析工作由该方法完成，处理工作是由处理器handler的handleToken()方法来实现
*/
public String parse(String text) {
// 验证参数问题，如果是null，就返回空字符串。
if (text == null || text.isEmpty()) {
return "";
}
// 下面继续验证是否包含开始标签，如果不包含，默认不是占位符，直接原样返回即可，否则继续执行。
int start = text.indexOf(openToken, 0);
if (start == -1) {
return text;
}
// 把text转成字符数组src，并且定义默认偏移量offset=0、存储最终需要返回字符串的变量builder，
// text变量中占位符对应的变量名expression。判断start是否大于-1(即text中是否存在openToken)，如果存在就执行下面代码
char[] src = text.toCharArray();
int offset = 0;
final StringBuilder builder = new StringBuilder();
StringBuilder expression = null;
while (start > -1) {
// 判断如果开始标记前如果有转义字符，就不作为openToken进行处理，否则继续处理
if (start > 0 && src[start - 1] == '\\') {
builder.append(src, offset, start - offset - 1).append(openToken);
offset = start + openToken.length();
} else {
//重置expression变量，避免空指针或者老数据干扰。
if (expression == null) {
expression = new StringBuilder();
} else {
expression.setLength(0);
}
builder.append(src, offset, start - offset);
offset = start + openToken.length();
int end = text.indexOf(closeToken, offset);
while (end > -1) {存在结束标记时
if (end > offset && src[end - 1] == '\\') {//如果结束标记前面有转义字符时
// this close token is escaped. remove the backslash and continue.
expression.append(src, offset, end - offset - 1).append(closeToken);
offset = end + closeToken.length();
end = text.indexOf(closeToken, offset);
} else {//不存在转义字符，即需要作为参数进行处理
expression.append(src, offset, end - offset);
offset = end + closeToken.length();
break;
}
}
if (end == -1) {
// close token was not found.
builder.append(src, start, src.length - start);
offset = src.length;
} else {
//首先根据参数的key（即expression）进行参数处理，返回?作为占位符
builder.append(handler.handleToken(expression.toString()));
offset = end + closeToken.length();
}
}
start = text.indexOf(openToken, offset);
}
if (offset < src.length) {
builder.append(src, offset, src.length - offset);
}
return builder.toString();
}
}
public class ParameterMappingTokenHandler implements TokenHandler {
private List<ParameterMapping> parameterMappings = new ArrayList<ParameterMapping>();
// context是参数名称 #{id} #{username}
public String handleToken(String content) {
parameterMappings.add(buildParameterMapping(content));
return "?";
}
private ParameterMapping buildParameterMapping(String content) {
ParameterMapping parameterMapping = new ParameterMapping(content);
return parameterMapping;
}
public List<ParameterMapping> getParameterMappings() {
return parameterMappings;
}
public void setParameterMappings(List<ParameterMapping> parameterMappings) {
this.parameterMappings = parameterMappings;
}
}
public class ParameterMapping {
private String content;
public ParameterMapping(String content) {
this.content = content;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
}
public interface TokenHandler {
String handleToken(String content);
}
5265
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
