mybatis使用小结-以备后用

数据持久化：

使用mybatis和mysql

先说一下总共用到了哪些功能：查询库中是否存在要创建的表、创建新表（返回1/0）、删除表、想表中插入数据、读取数据（返回List）、查询表中所有字段。

使用的sql语句全部是动态配置，可自己拼接。

先上目录结构：

附上RosterInfoMapper代码，如下：

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-////DTD Mapper 3.0//EN""/dtd/mybatis-3-mapper.dtd"><mapper namespace=".mybatis.util.RosterInfoMapper"><!-- id：用来配合namespace来定位这个操作，也就是在调用时，将采用cn.kolbe.mybatis.StudentMapper.getStudent --><!-- resultType：表示返回值类型，必须是全类名，MyBatis将自动为我们将查询结果封装成指定对象 --><!-- parameterType：表示传进来的参数的类型，因为传的是id主键，所以是int型 --><!-- 查询表是否存在 --><select id="selectTableSql" parameterType="map" resultType="Integer"> ${sql} </select><!-- 查询表中所有数据 .mybatis.util.selectResponse --><select id="selectByNameSql" parameterType="map" resultType="java.util.Map"> ${sql} </select>

<!-- 查询表中所有字段 --><select id="selectTableColumnsSql" parameterType="map" resultType="java.lang.String"> ${sql}</select><!-- 创建表 --><update id="createTableSql" parameterType="map"> ${sql} </update><!-- 删除表 --><update id="deleteTableSql" parameterType="map"> ${sql} </update><!-- 插入数据 --><insert id="insertSql" parameterType="map"> ${sql} </insert></mapper>

附上mybatis.xml代码，如下：

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configurationPUBLIC "-////DTD Config 3.0//EN""/dtd/mybatis-3-config.dtd"><configuration><environments default="development"><environment id="development"><!-- 事务管理类型，JDBC表示直接使用JDBC的提交和回滚设置，依赖于数据源得到的连接来管理事务 --><transactionManager type="JDBC" /><!-- 数据库连接池POOLED表示使用数据库连接池 --><dataSource type="POOLED"><property name="driver" value="com.mysql.jdbc.Driver" /><property name="url" value="jdbc:mysql://172.16.0.244:3306/test" /><property name="username" value="root" /><property name="password" value="root01" /></dataSource></environment></environments><mappers><!-- SQL代码和映射信息配置文件 --><mapper resource="wilcom/com/mybatis/util/RosterInfoMapper.xml" /></mappers></configuration>

附上RosterDao.java代码，如下：

package .mybatis.util;import java.io.IOException;import java.io.InputStream;import java.sql.SQLException;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Map.Entry;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import org.apache.logging.log4j.LogManager;import org.apache.logging.log4j.Logger;/*** 数据库连接类* @author 小啊屁* @version 1.0* */public class RosterDao {public final Logger logger = LogManager.getLogger(RosterDao.class);private SqlSession session = null;private InputStream in = null;Map<String , Object> map=new HashMap<String , Object>();public RosterDao(){// 1. 加载MyBatis的配置文件：mybatis.xml（它也加载关联的映射文件，也就是mappers结点下的映射文件）in = this.getClass().getClassLoader().getResourceAsStream("mybatis.xml");// 2. SqlSessionFactoryBuidler实例将通过输入流调用build方法来构建 SqlSession 工厂SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);// 3. 通过工厂获取 SqlSession 实例，SqlSession 完全包含了面向数据库执行 SQL 命令所需的所有方法。session = sqlSessionFactory.openSession();}/*** 查询数据库中是否已经存在表* @param String 表名* @return boolean* @throws SQLException * */public boolean selectWhetherTableExistsSql(String tableName) throws SQLException{String dbUrl = session.getConnection().getMetaData().getURL();String dbName = dbUrl.substring(dbUrl.lastIndexOf('/')+1);logger.info("DataBase Name: " + dbName);String sql2 = "SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '"+dbName+"' and TABLE_NAME = '"+tableName+"'";map.put("sql",sql2); String statement2 = ".mybatis.util.RosterInfoMapper.selectTableSql";int temp = session.selectOne(statement2, map);logger.info("Number of Table: " + temp);if(temp>0){logger.info("The Table " + tableName + " is already exists !");return true;}else {logger.info("The Table " + tableName + " is not exists, Please Create !");return false;}}/*** 创建表* @param1 String 表名* @param2 String 列名及列数据类型* @return null* */public void createTableSql(String tableName, String columns){String sql = "create table " + tableName + " " + columns;map.put("sql",sql); String statement0 = ".mybatis.util.RosterInfoMapper.createTableSql";session.update(statement0, map);logger.info("Table RosterInfo has been Created !");}/*** 删除表* @param1 String 表名* @return null* */public void deleteTableSql(String tableName){String sql = "drop table " + tableName;map.put("sql",sql); String statement0 = ".mybatis.util.RosterInfoMapper.deleteTableSql";session.update(statement0, map);logger.info("Table RosterInfo has been Deleted !");}/*** 向表中插入数据(接口传入文件路径时使用)* @param String 表名* @param String 要插入的数据* @return null* */public void insertFileSql(String name, String value){String sql1 = "insert into " + name + " value " + value;map.put("sql",sql1); String statement1 = ".mybatis.util.RosterInfoMapper.insertSql";session.insert(statement1, map);mit();}/*** 向表中插入数据(接口传入json字串是使用)* @param String 表名* @param String 要插入的数据* @return null* */public void insertJsonSql(String name, String value){String sql1 = "insert into " + name + value;map.put("sql",sql1); String statement1 = ".mybatis.util.RosterInfoMapper.insertSql";session.insert(statement1, map);mit();}/*** 把Map的Key/Value拼接成数据库的插入语句的形式* @param Map * @return String* */public String jointSql(Map<String, ?> map){StringBuffer strKey = new StringBuffer();strKey.append("(");StringBuffer strValue = new StringBuffer();strValue.append("('");int i = 0;int length = map.size();for (Entry<String, ?> entry : map.entrySet()) {if(i<length-1){strKey.append(entry.getKey()).append(",");strValue.append(entry.getValue().toString()).append("','");i++;} else {strKey.append(entry.getKey()).append(")");strValue.append(entry.getValue().toString()).append("')");}}String str = strKey.toString() + " value " + strValue.toString();return str;}/*** 拼接插入数据的SQL语句* @author sunlu* @param String[] * @return String* */public String jointSql(String[] arr, int[] arrNum){int length = arrNum.length;StringBuffer value = new StringBuffer();value.append("('");for(int i = 0; i < length - 1; i++){value.append(arr[arrNum[i]] + "','");}value.append(arr[arrNum[length-1]] + "')");logger.info(value.toString());return value.toString();}/*** 查询表中数据* @param String 表名* @return List* */public List<Object> selectByNameSql(String name){String dbUrl = null;try {dbUrl = session.getConnection().getMetaData().getURL();} catch (SQLException e) {e.printStackTrace();}String dbName = dbUrl.substring(dbUrl.lastIndexOf('/')+1);logger.info("DataBase Name: " + dbName);String sql = "SELECT * from rosterInfo where name = '" + name + "'";logger.info(sql);map.put("sql",sql); String statement2 = ".mybatis.util.RosterInfoMapper.selectByNameSql";List<Object> temp = session.selectList(statement2, map);logger.info("Result: " + temp);return temp;}/** * 查询表中所有字段 * @param String 表名 * @return boolean * */public String[] selectTableColumnSql(String name){List<String> temp = null;String sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '" + name + "'";logger.info(sql);map.put("sql",sql);String statement2 = ".mybatis.util.RosterInfoMapper.selectTableColumnsSql";temp = (List) session.selectList(statement2, map);int size = temp.size(); String[] arr = (String[])temp.toArray(new String[size]);logger.info(arr[0]);return arr;}/*** 关闭连接* @param null* @return null* */public void close() throws IOException {in.close();session.close();}}