项目大致目录如下:
com.icsc.dao为Mybatis原始dao的开发。后面会和mapper代理的方法开发一起说。
- package com.icsc.jdbc;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- /**
- * JDBC连接MySql数据库测试
- * @author W12179
- *
- */
- public class JdbcTest {
- public static void main(String[] args) {
- //1.准备参数
- //数据库连接
- Connection conn = null;
- //预编译的sql语句
- PreparedStatement pstm = null;
- //返回结果集
- ResultSet rs = null;
- try {
- //2.加载驱动
- Class.forName("com.mysql.jdbc.Driver");
- //3.获取连接
- conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sys?characterEncoding=utf-8","root","admin");
- //4.准备sql语句
- String sql = "SELECT * FROM USER WHERE ID = ?";
- pstm = conn.prepareStatement(sql);
- //设置参数,从第一参数开始,
- pstm.setString(1, "1");
- //5.执行预编译的sql语句,获取结果集
- rs = pstm.executeQuery();
- //6.操作结果集
- while(rs.next()){
- System.out.println(rs.getString("id")+","+rs.getString("name")+","+rs.getString("sex"));
- }
- } catch (Exception e) {
- e.printStackTrace();
- }finally{
- try {
- //7.依次释放资源,注意顺序
- if(rs!=null){
- rs.close();
- }
- if(pstm!=null){
- pstm.close();
- }
- if(conn!=null){
- conn.close();
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }
- }
能打印数据库对应的数据,则说明连接数据库成功。
2.加入jar包
3.创建log4j.properties
- log4j.rootLogger=DEBUG,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.org.apache=INFO
4.创建Mybatis的SqlMapConfig.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>
- <!--
- 与springMVC整合后,这部分由Spring管理,不需要写了。
- -->
- <environments default="development">
- <environment id="development">
- <transactionManager type="JDBC"/>
- <dataSource type="POOLED">
- <property name="driver" value="com.mysql.jdbc.Driver"/>
- <property name="username" value="root"/>
- <property name="password" value="admin"/>
- <property name="url" value="jdbc:mysql://localhost:3306/sys?characterEncoding=utf-8"/>
- </dataSource>
- </environment>
- </environments>
- <!--引入mapper.xml -->
- <mappers>
- <mapper resource="sqlmap/User.xml"/>
- </mappers>
- </configuration>
5.创建实体类和对应的mapper.xml,以user为例
User.Java
- package com.icsc.entity;
- /**
- * 用户实体类
- * @author W12179
- *
- */
- public class User {
- private String name;
- private Integer sex;
- private Integer id;
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public Integer getSex() {
- return sex;
- }
- public void setSex(Integer sex) {
- this.sex = sex;
- }
- public Integer getId() {
- return id;
- }
- public void setId(Integer id) {
- this.id = id;
- }
- @Override
- public String toString() {
- return "User [name=" + name + ", sex=" + sex + ", id=" + id + "]";
- }
- }
User.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="test">
- <!-- 根据id,查询一笔资料 -->
- <select id="findUserById" parameterType="int" resultType="com.icsc.entity.User">
- select * from user where id = #{id}
- </select>
- <!--
- #{}:表示一个占位符,也就是?,当里面的参数是简单数据类型的时候,可以是value,也可以是任意。
- ${}:表示字符串连接符号。当里面的参数是简单数据类型的时候,必须写成value
- resultType:表示输出单条记录的类型。
- parameterType:输入参数的类型
- -->
- <!-- 根据name模糊查询 -->
- <select id="findUserByName" parameterType="String" resultType="com.icsc.entity.User">
- select * from user where name like '%${value}%'
- </select>
- <!-- 新增资料 -->
- <insert id="insertUser" parameterType="com.icsc.entity.User">
- <!--
- 获取自增主键。
- keyProperty:主键放入的属性。
- order:相对于insert语句的执行顺序。
- resultType:主键的返回类型
- -->
- <selectKey keyProperty="id" order="AFTER" resultType="int">
- select LAST_INSERT_ID()
- </selectKey>
- insert into user (name,sex) values(#{name},#{sex})
- </insert>
- <!--
- <insert id="insertUser" parameterType="com.icsc.entity.User">
- 获取非自增主键。(UUID)
- keyProperty:主键放入的属性。
- order:相对于insert语句的执行顺序。
- resultType:主键的返回类型
- <selectKey keyProperty="id" order="BEFORE" resultType="String">
- select UUID()
- </selectKey>
- insert into user (id,name,sex) values(#{id},#{name},#{sex})
- </insert>-->
- <!-- 根据id删除一笔资料 -->
- <delete id="deleteUser" parameterType="int">
- delete from user where id = #{id}
- </delete>
- <!-- 根据id修改一笔资料 -->
- <update id="updateUser" parameterType="com.icsc.entity.User">
- update user set name = #{name},sex=#{sex} where id =#{id}
- </update>
- </mapper>
6.测试
- package com.icsc.MybatisFirst;
- 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.junit.Test;
- import com.icsc.entity.User;
- /**
- * 测试User.xml
- * @author W12179
- *
- */
- public class MybatisFirst {
- @Test
- public void findUserById() throws Exception{
- //获取sqlSessionFactory
- SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("SqlMapConfig.xml"));
- //获取sqlSession,注意SqlSession是线程不安全的,要定义在方法里
- SqlSession sqlSession = sqlSessionFactory.openSession();
- //通过SQLSession操作数据库
- User user = sqlSession.selectOne("test.findUserById", 1);
- System.out.println(user);
- //释放资源
- sqlSession.close();
- }
- @Test
- public void findUserByName() throws Exception{
- SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("SqlMapConfig.xml"));
- SqlSession sqlSession = sqlSessionFactory.openSession();
- List<User> list = sqlSession.selectList("test.findUserByName", "张");
- System.out.println(list);
- sqlSession.close();
- }
- @Test
- public void insertUser() throws Exception{
- SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("SqlMapConfig.xml"));
- SqlSession sqlSession = sqlSessionFactory.openSession();
- User user = new User();
- user.setName("李思");
- user.setSex(2);
- int n= sqlSession.insert("test.insertUser", user);
- System.out.println(n);
- System.out.println(user.getId());
- //提交事务
- sqlSession.commit();
- sqlSession.close();
- }
- @Test
- public void deleteUser() throws Exception{
- SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("SqlMapConfig.xml"));
- SqlSession sqlSession = sqlSessionFactory.openSession();
- int n= sqlSession.delete("test.deleteUser", 4);
- System.out.println(n);
- sqlSession.commit();
- sqlSession.close();
- }
- @Test
- public void updateUser() throws Exception{
- SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("SqlMapConfig.xml"));
- SqlSession sqlSession = sqlSessionFactory.openSession();
- User user = new User();;
- user.setId(5);
- user.setName("李思aa");
- user.setSex(1);
- int n= sqlSession.insert("test.updateUser", user);
- System.out.println(n);
- sqlSession.commit();
- sqlSession.close();
- }
- }