用MyBatis进行简单的增删改查
项目结构如下:
各部分代码
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 引入配置信息文件 --> <properties resource="mysql.properties" /> <!-- 必须放在environments和properties之间,不然解析xml会发生错误 --> <typeAliases> <typeAlias alias="User" type="com.test.bean.User" /> </typeAliases> <environments default="demo"> <environment id="demo"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property value="${driver}" name="driver" /> <property value="${url}" name="url" /> <property value="${username}" name="username" /> <property value="${password}" name="password" /> </dataSource> </environment> </environments> <mappers> <mapper resource="com/test/bean/UserMapper.xml" /> </mappers> </configuration>
mysql.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8 user=root password=
log4j.properties用来打印日志和MyBatis的sql语句
log4j.rootLogger=DEBUG, Console #Console log4j.appender.Console=org.apache.log4j.ConsoleAppender log4j.appender.Console.layout=org.apache.log4j.PatternLayout log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n log4j.logger.java.sql.ResultSet=INFO log4j.logger.org.apache=INFO log4j.logger.java.sql.Connection=DEBUG log4j.logger.java.sql.Statement=DEBUG log4j.logger.java.sql.PreparedStatement=DEBUG
User.java
package com.test.bean; import java.util.Date; public class User { private int uid; private String token; private String account; private String password; private String userName; private Date createdDate; public int getUid() { return uid; } public void setUid(int uid) { this.uid = uid; } public String getToken() { return token; } public void setToken(String token) { this.token = token; } public String getAccount() { return account; } public void setAccount(String account) { this.account = account; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public Date getCreatedDate() { return createdDate; } public void setCreatedDate(Date createdDate) { this.createdDate = createdDate; } @Override public String toString() { return "-------\nuid:"+uid+"\ntoken:"+token+"\naccount:"+account+"\nuserName:"+userName+"\ncreatedDate:"+createdDate; } }
UserMapper.java 操作的接口类
package com.test.bean; import java.util.List; import org.apache.ibatis.annotations.Insert; import org.apache.ibatis.annotations.Select; import org.apache.ibatis.annotations.Update; public interface UserMapper { //****** 查询用户 ****** public User selectUser(String id); @Select("select * from user where uid = #{id}") public User selectUser2(String id); @Select("select * from user where user_name like \"%\"#{name}\"%\"") public List<User> fuzzyQuery(String name); //****** 查询所有用户 ****** public List<User> selectAll(); //****** 插入用户 ****** public int insertUser(User user); @Insert("insert into user(account,user_name,created_date) values(#{account},#{userName},now())") public int insertUser2(User user); //******* 修改用户 ******* @Update("update user set user_name=#{userName} where uid=#{uid}") public int updateUser(User user); //******* 删除 ******* @Update("delete from user where uid = #{id}") public int deleteUser(int id); //******* 批量插入 ******* public int insertBatch(List list); }
UserMapper.xml 配置文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.test.bean.UserMapper"> <sql id="userColuns"> uid,account,user_name </sql> <!-- 查询 --> <select id="selectUser" parameterType="java.lang.String" resultType="User"> select <include refid="userColuns"/> from user where uid = #{id}; </select> <select id="selectAll" resultType="User"> select * from user; </select> <!-- 插入 --> <insert id="insertUser" parameterType="User"> insert into user(account,user_name,created_date) values(#{account},#{userName},now()) </insert> <!-- 批量插入 --> <insert id="insertBatch" parameterType="java.util.List"> insert into user(account,user_name,created_date) values <foreach collection="list" item="item" index="index" separator="," > (#{item.account},#{item.userName},now()) </foreach> </insert> </mapper>
测试类
Main.java
import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.apache.log4j.Logger; import com.test.bean.User; import com.test.bean.UserMapper; public class Main { private SqlSession session = null; private UserMapper userMapper = null; private final Logger log = Logger.getLogger(Main.class); public static void main(String[] args) { new Main(); } public Main(){ session = initSession(); userMapper = session.getMapper(UserMapper.class); testDelete(); testUpdate(); testInsert(); testSelect(); if(null != session){ session.commit(); session.close(); } } private void testDelete(){ System.out.println(userMapper.deleteUser(10000)); } private void testUpdate(){ User user = new User(); user.setUid(10000); user.setUserName("修改"); System.out.println(userMapper.updateUser(user)); } private void testInsert(){ User user = new User(); user.setUid(10000); user.setAccount("aaaaa"); user.setUserName("展示"); System.out.println(user.toString()); System.out.println(userMapper.insertUser2(user)); //批量插入 User u1 = new User(); u1.setAccount("user1"); u1.setUserName("user1"); User u2 = new User(); u2.setAccount("user3"); u2.setUserName("user3"); List<User> l = new ArrayList<User>(); l.add(u1); l.add(u2); userMapper.insertBatch(l); } private void testSelect(){ User user = userMapper.selectUser("10003"); System.out.println(user.toString()); user = userMapper.selectUser2("10000"); System.out.println(user.toString()); List<User> list = userMapper.selectAll(); for(User u : list){ System.out.println(u.toString()); } List<User> list2 = userMapper.fuzzyQuery("展"); for(User u : list2){ System.out.println(u.toString()); } } private SqlSession initSession(){ //获取配置文件的输入流 InputStream is; try { is = Resources.getResourceAsStream("mybatis-config.xml"); //获取我们的SqlSessionFactory(相当于Hibernate的SessionFactory);SqlSessionFactoryBuilder有点类似于Hibernate的Configuration。 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is); //获取一个Session return sqlSessionFactory.openSession(); } catch (IOException e) { e.printStackTrace(); } return null; } }