Mybatis数据库连接,以及用语句.

首先创建一个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文件.