首先创建一个db.properties配置文件
#MYSQL config
jdbc.driverClassName=com.mysql.jdbc.jdbc2.optional.MysqlDataSource
jdbc.url=jdbc:mysql://localhost:3306/db_mysql?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
jdbc.username=root
jdbc.password=root
数据源的配置(使用hikariConfig配置获取数据源)
<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
<property name="dataSourceProperties">
<props>
<prop key="url">${jdbc.url}</prop>
<prop key="user">${jdbc.username}</prop>
<prop key="password">${jdbc.password}</prop>
</props>
</property>
<property name="connectionTestQuery" value="SELECT 1"/>
<!--poolName属性自定义即可 -->
<property name="poolName" value="springHikariCP"/>
<property name="dataSourceClassName" value="${jdbc.driverClassName}"/>
<!-- 连接只读数据库时配置为true, 保证安全 -->
<property name="readOnly" value="false"/>
<!-- 等待连接池分配连接的最大时长(毫秒),超过这个时长还没可用的连接则发生SQLException, 缺省:30秒 -->
<property name="connectionTimeout" value="60000"/>
<!-- 一个连接idle状态的最大时长(毫秒),超时则被释放(retired),缺省:10分钟 -->
<property name="idleTimeout" value="600000"/>
<!-- 一个连接的生命时长(毫秒),超时而且没被使用则被释放(retired),缺省:30分钟,建议设置比数据库超时时长少30秒 -->
<property name="maxLifetime" value="1200000"/>
<!-- 连接池中允许的最大连接数。缺省值:10;推荐的公式:((core_count * 2) + effective_spindle_count) -->
<property name="maximumPoolSize" value="50"/>
<!-- 用来指定验证连接有效性的超时时 -->
<property name="validationTimeout" value="30000"/>
</bean>
<!-- HikariCP dataSource配置 -->
<bean id="hikariDataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
<constructor-arg ref="hikariConfig"/>
</bean>
实体类
import java.util.Date;
public class User {
private int uId;
private String uName;
private String uPwd;
private String uPhone;
private double uBalance;
private int uState;
private int uRole;
private String uImage;//用户头像
private Date uBirth;
get/set省略........
常用接口方法
public interface UserMapper {
public User findUserByUid(int uId) throws Exception;
public User findUserByUid2(int uId) throws Exception;
public int deleteUserByUid(int uId) throws Exception;
public int addUser(User user) throws Exception;
public int updateUser(User user) throws Exception;
//根据用户编号查询用户信息,根据用户类别查询用户信息,根据用户状态
public List<User> findUsersByConditions(User user) throws Exception;
//返回值为Map的查询方法
public List<Map<String,Object>> findUserMap(User user) throws Exception;
//查询foreach1
public List<User> foreachTest1(Object[] uIds) throws Exception;
//查询foreach2
public List<User> foreachTest2(List<Integer> uIds) throws Exception;
public int updateUser1(User user) throws Exception;
}
使用mapper包下新建UserMapper.xml文件,进行sql拼接
<mapper namespace="cn.neu.mybatis.mapper.UserMapper">
<select id="findUserByUid" parameterType="int"
resultType="User">
SELECT U_ID uId,U_NAME uName,U_PWD uPwd,U_PHONE
uPhone,U_BALANCE uBalance,U_STATE uState,U_ROLE uRole,U_IMAGE
uImage,U_BIRTH uBirth FROM tb_user WHERE U_ID = #{value}
</select>
<!-- 根据用户名模糊查询 #{}:标识一个占位符 ${}:可以进行字符串拼接,例如模糊查询 SQL语句需要拼接,务必使用 ${}, 不用拼接,直接获取值使用
#{}. -->
<select id="findUsersByUname" parameterType="String"
resultType="User">
SELECT U_ID uId,U_NAME uName,U_PWD uPwd,U_PHONE
uPhone,U_BALANCE uBalance,U_STATE uState,U_ROLE uRole,U_IMAGE
uImage,U_BIRTH uBirth FROM tb_user WHERE U_NAME like '%${value}%'
</select>
<!-- 添加用户信息 -->
<insert id="addUser" parameterType="User">
insert into tb_user(U_NAME,U_PWD,U_PHONE) values(#{uName},#{uPwd},#{uPhone})
</insert>
<!-- 删除方法 -->
<delete id="deleteUserByUid" parameterType="int">
DELETE FROM tb_user
WHERE U_ID = #{uId}
</delete>
<!-- 修改方法 -->
<update id="updateUser"
parameterType="User">
UPDATE tb_user SET
U_NAME=#{uName},U_PHONE=#{uPhone},U_IMAGE=#{uImage},U_PWD=#{uPwd}
where U_ID = #{uId}
</update>
<!-- sql标签进行标签定义 -->
<sql id="selectAllColumns">
SELECT U_ID uId,U_NAME uName,U_PWD uPwd,U_PHONE
uPhone,U_BALANCE uBalance,U_STATE uState,U_ROLE uRole,U_IMAGE
uImage,U_BIRTH uBirth FROM tb_user
</sql>
<select id="findUserByUid2" parameterType="int"
resultType="User">
<include refid="selectAllColumns"></include>
WHERE U_ID = #{value}
</select>
<!-- 使用where标签结合if标签实现一个语句多个功能 -->
<sql id="query_user_where">
<if test="uId!=0 and uId!='' and uId!=null">
AND U_ID = #{uId}
</if>
<if test="uName!='' and uName!=null">
AND U_NAME = #{uName}
</if>
<if test="uPwd!='' and uPwd!=null">
AND U_PWD = #{uPwd}
</if>
</sql>
<select id="findUsersByConditions" parameterType="User"
resultType="User">
<include refid="selectAllColumns"></include>
<!-- where标签可以控制where标签中的内容,如果if判断语句中返回false,不会添加对应语句,返回true会添加对应语句 -->
<!-- where标签会判断第一个语句是否有and 如果有会自动删除 -->
<where>
<include refid="query_user_where"></include>
</where>
</select>
<!-- 使用Map作为返回值类型 -->
<select id="findUserMap" parameterType="User"
resultType="java.util.Map">
<include refid="selectAllColumns"></include>
<where>
<include refid="query_user_where"></include>
</where>
</select>
<!-- foreach使用 -->
<select id="foreachTest1" resultType="User">
<include refid="selectAllColumns"></include>
<if test="array!=null and array.length>=0">
where U_ID in
<!-- collection需要遍历的内容,item遍历每一个选项,separator是分隔符,#{i}得到数组每一个值 -->
<foreach collection="array" item="i" open="(" separator=","
close=")">
#{i}
</foreach>
</if>
</select>
<select id="foreachTest2" resultType="User">
<include refid="selectAllColumns"></include>
<if test="list!=null and list.size()>=0">
where U_ID in
<!-- collection需要遍历的内容,item遍历每一个选项,separator是分隔符,#{i}得到数组每一个值 -->
<foreach collection="list" item="i" open="(" separator=","
close=")">
#{i}
</foreach>
</if>
</select>
<update id="updateUser1" parameterType="User">
<if test="uName!=null or uPwd!=null or uPhone!=null">
update tb_user
<!-- set标签会自动去掉最后一个逗号完成拼接 -->
<set>
<if test="uName!=null and uName!=''">
U_NAME = #{uName},
</if>
<if test="uPwd!=null and uPwd!=''">
U_PWD = #{uPwd},
</if>
<if test="uPhone!=null and uPhone!=''">
U_PHONE = #{uPhone},
</if>
</set>
where U_ID = #{uId}
</if>
</update>
</mapper>
测试
public class UserMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws Exception {
InputStream inputStream = Resources.getResourceAsStream("config/sqlMapConfig.xml");
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void testFindUserByUid() throws Exception {
//Spring可以使用自动装载,协助实例化UserMapper,直接调用即可
SqlSession session = sqlSessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = userMapper.findUserByUid(2);
System.out.println(user.toString());
session.close();
}
@Test
public void testDeleteUserByUid() throws Exception {
SqlSession session = sqlSessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
int i = userMapper.deleteUserByUid(17);
System.out.println("删除了"+i+"条记录");
session.commit();
session.close();
}
@Test
public void testUpdateUser() throws Exception {
SqlSession session = sqlSessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = userMapper.findUserByUid(7);
user.setuName("你好");
user.setuPwd("1234");
user.setuPhone("23156587691");
int i = userMapper.updateUser(user);
System.out.println("修改了"+i+"条记录");
session.commit();
session.close();
}
@Test
public void testaddUser() throws Exception {
SqlSession session = sqlSessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = new User("ttd","1234","13825436380");
int i = userMapper.addUser(user);
System.out.println("添加了"+i+"条记录");
session.commit();
session.close();
}
@Test
public void testfindUsersByConditions() throws Exception {
SqlSession session = sqlSessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = new User();
user.setuId(1);
List<User> users = userMapper.findUsersByConditions(user);
for(User u : users) {
System.out.println(u.toString());
}
session.close();
}
@Test
public void testfindUserMap() throws Exception {
SqlSession session = sqlSessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = new User();
user.setuId(1);
List<Map<String,Object>> users = userMapper.findUserMap(user);
for(Map<String,Object> m : users) {
System.out.println(m.get("uId"));
System.out.println(m.get("uName"));//键的名字对应实体类的属性名
}
session.close();
}
@Test
public void foreachTest1() throws Exception {
SqlSession session = sqlSessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
Object[] uIds = {1,2,6,7};
List<User> users = userMapper.foreachTest1(uIds);
for(User u : users) {
System.out.println(u.toString());
}
session.close();
}
@Test
public void foreachTest2() throws Exception {
SqlSession session = sqlSessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
List<Integer> uIds = new ArrayList<>();
uIds.add(1);
uIds.add(2);
uIds.add(7);
List<User> users = userMapper.foreachTest2(uIds);
for(User u : users) {
System.out.println(u.toString());
}
session.close();
}
@Test
public void testupdateUser1() throws Exception {
SqlSession session = sqlSessionFactory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = new User();
user.setuId(2);
user.setuName("cpx");
int i = userMapper.updateUser1(user);
System.out.println("修改了"+i+"条记录");
session.commit();
session.close();
}
}
该测试类中首先写了init方法用作初始化,加载了sqlMapConfig.xml文件.