一、传统的JDBC连接方式
首先我们先看下传统的jdbc取数据的步骤和流程
public class JdbcDemo { static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://ip:3306/test?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true"; // Database credentials static final String USER = "root"; static final String PASS = "123456"; public void QueryPreparedStatementDemo() { Connection conn = null; PreparedStatement stmt = null; List<TUser> users = new ArrayList<>(); try { // STEP 1: 注册mysql的驱动 Class.forName("com.mysql.jdbc.Driver"); // STEP 2: 获得一个连接 System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL, USER, PASS); // STEP 3: 创建一个查询 System.out.println("Creating statement..."); String sql; sql = "SELECT * FROM t_user where userName= ? "; stmt = conn.prepareStatement(sql); stmt.setString(1, "zhangsan");//处理占位符 System.out.println(stmt.toString());//打印sql ResultSet rs = stmt.executeQuery(); // STEP 4: 从resultSet中获取数据并转化成bean while (rs.next()) { System.out.println("------------------------------"); // Retrieve by column name TUser user = new TUser(); user.setId(rs.getInt("id")); user.setUserName(rs.getString("userName")); user.setRealName(rs.getString("realName")); user.setMobile(rs.getString("mobile")); user.setEmail(rs.getString("email")); user.setNote(rs.getString("note")); System.out.println(user.toString()); users.add(user); } // STEP 5: 关闭连接 rs.close(); stmt.close(); conn.close(); } catch (SQLException se) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) stmt.close(); } catch (SQLException se2) { }// nothing we can do try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); } } System.out.println("-------------------------"); System.out.println("there are " + users.size() + " users in the list!"); } public static void main(String[] args) { JdbcDemo jd = new JdbcDemo(); jd.QueryPreparedStatementDemo(); } }
执行结果
从上面的代码上来看，传统的JDBC编程存在的弊端：
- 数据库连接创建、释放频繁造成系统资源浪费，影响系统性能，可使用数据库连接池解决此问题。
- sql语句中在代码中硬编码，代码不易维护，sql变动需要改变java代码。
- 使用preparedStatement向占有位符号传参数存在硬编码。where条件不一定，修改sql就要修改代码，不易于维护。
- 对结果集解析存在硬编码，sql变化导致解析代码变化，没有动态映射结果集。
二、什么是ORM？
ORM即对象关系映射（Object Relational Mapping），用于实现面向对象编程语言里不同类型系统的数据之间的转换。简单的说，ORM是通过使用描述对象和数据库之间映射的元数据，将程序中的对象自动持久化到关系数据库中
ORM解决的主要问题是对象关系的映射。域模型和关系模型分别是建立在概念模型的基础上的。域模型是面向对象的，而关系模型是面向关系的。一般情况下，一个持久化类和一个表对应，类的每个实例对应表中的一条记录，类的每个属性对应表的每个字段。
ORM技术特点：
- 提高了开发效率。由于ORM可以自动对Entity对象与数据库中的Table进行字段与属性的映射，所以我们实际可能已经不需要一个专用的、庞大的数据访问层。
- ORM提供了对数据库的映射，不用sql直接编码，能够像操作对象一样从数据库获取数据。
三、ORM持久层框架Mybatis使用
1、什么是mybatis？
Mybatis前身是iBatis,其源于“Internet”和“ibatis”的组合，本质是一种半自动的ORM框架，除了POJO和映射关系之外，还需要编写SQL语句
2、Mybatis的使用
1、新增配置文件
<?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="db.properties"/> <settings> <!-- 设置自动驼峰转换 --> <setting name="mapUnderscoreToCamelCase" value="true" /> <!-- 开启懒加载 --> <!-- 当启用时，有延迟加载属性的对象在被调用时将会完全加载任意属性。否则，每种属性将会按需要加载。默认：true --> <setting name="aggressiveLazyLoading" value="false" /> </settings> <!-- 别名定义 --> <typeAliases> <package name="com.enjoylearning.mybatis.entity" /> </typeAliases> <plugins> <plugin interceptor="com.enjoylearning.mybatis.Interceptors.ThresholdInterceptor"> <property name="threshold" value="10"/> </plugin> <plugin interceptor="com.github.pagehelper.PageInterceptor"> <property name="pageSizeZero" value="true" /> </plugin> </plugins> <!--配置environment环境 --> <environments default="development"> <!-- 环境配置1，每个SqlSessionFactory对应一个环境 --> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://ip:3306/database?useUnicode=true" /> <property name="username" value="root" /> <property name="password" value="123456" /> </dataSource> </environment> </environments> <!-- 映射文件，mapper的配置文件 --> <mappers> <!--直接映射到相应的mapper文件 --> <mapper resource="sqlmapper/TUserMapper.xml"/> <mapper resource="sqlmapper/TUserTestMapper.xml" /> <mapper resource="sqlmapper/TRoleMapper.xml" /> <mapper resource="sqlmapper/TJobHistoryMapper.xml" /> <mapper resource="sqlmapper/TPositionMapper.xml" /> <mapper resource="sqlmapper/THealthReportFemaleMapper.xml" /> <mapper resource="sqlmapper/THealthReportMaleMapper.xml" /> </mappers> </configuration>
2、编写pojo类
import java.io.Serializable; import java.util.List; import org.apache.ibatis.annotations.Param; import com.mysql.jdbc.Blob; public class TUser implements Serializable{ private Integer id; private String userName; private String realName; private Byte sex; private String mobile; private String email; private String note; private TPosition position; private List<TJobHistory> jobs ; private List<HealthReport> healthReports; private List<TRole> roles; /* public TUser(Integer id, String userName) { super(); this.id = id; this.userName = userName; } */ @Override public String toString() { String positionId= (position == null ? "" : String.valueOf(position.getId())); return "TUser [id=" + id + ", userName=" + userName + ", realName=" + realName + ", sex=" + sex + ", mobile=" + mobile + ", email=" + email + ", note=" + note + ", positionId=" + positionId + "]"; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } public String getRealName() { return realName; } public void setRealName(String realName) { this.realName = realName; } public Byte getSex() { return sex; } public void setSex(Byte sex) { this.sex = sex; } public String getMobile() { return mobile; } public void setMobile(String mobile) { this.mobile = mobile; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getNote() { return note; } public void setNote(String note) { this.note = note; } public TPosition getPosition() { return position; } public void setPosition(TPosition position) { this.position = position; } public List<TJobHistory> getJobs() { return jobs; } public void setJobs(List<TJobHistory> jobs) { this.jobs = jobs; } public List<HealthReport> getHealthReports() { return healthReports; } public void setHealthReports(List<HealthReport> healthReports) { this.healthReports = healthReports; } public List<TRole> getRoles() { return roles; } public void setRoles(List<TRole> roles) { this.roles = roles; } }
3、编写mapper接口层
import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.Param; public interface TUserMapper { TUser selectByPrimaryKey(Integer id); List<TUser> selectAll(); List<TUser> selectTestResultMap(); List<TUser> selectAllTest(); int deleteByPrimaryKey(Integer id); int insert1(TUser record); int insert2(TUser record); int insertSelective(TUser record); int updateByPrimaryKeySelective(TUser record); int updateByPrimaryKey(TUser record); List<TUser> selectUserPosition1(); List<TUser> selectUserPosition2(); List<TUser> selectUserJobs1(); List<TUser> selectUserJobs2(); List<TUser> selectUserHealthReport(); List<TUser> selectUserRole(); List<TUser> selectByEmailAndSex1(Map<String, Object> param); List<TUser> selectByEmailAndSex2(@Param("email")String email,@Param("sex")Byte sex); List<TUser> selectByEmailAndSex3(EmailSexBean esb); List<TUser> selectBySymbol(@Param("tableName")String tableName, @Param("inCol")String inCol, @Param("orderStr")String orderStr, @Param("userName")String userName); List<TUser> selectIfOper(@Param("email")String email,@Param("sex")Byte sex); List<TUser> selectIfandWhereOper(@Param("email")String email,@Param("sex")Byte sex); List<TUser> selectChooseOper(@Param("email")String email,@Param("sex")Byte sex); int updateIfOper(TUser record); int updateIfAndSetOper(TUser record); int insertIfOper(TUser record); List<TUser> selectForeach4In(String[] names); int insertForeach4Batch(List<TUser> users); }
4、编写mapper.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.enjoylearning.mybatis.mapper.TUserMapper"> <cache></cache> <select id="selectByPrimaryKey" resultType="TUser" > select id, userName, realName, sex, mobile, email, note from t_user where id = #{id,jdbcType=INTEGER} </select> <select id="selectAll" resultType="TUser"> select id, userName, realName, sex, mobile, email, note from t_user </select> <resultMap id="UserResultMap" type="TUser" autoMapping="true"> <id column="id" property="id" /> <result column="userName" property="userName"/> <result column="realName" property="realName" /> <result column="sex" property="sex" /> <result column="mobile" property="mobile" /> <result column="email" property="email" /> <result column="note" property="note" /> <association property="position" javaType="TPosition" columnPrefix="post_"> <id column="id" property="id"/> <result column="name" property="postName"/> <result column="note" property="note"/> </association> </resultMap> <select id="selectTestResultMap" resultMap="UserResultMap" > select a.id, userName, realName, sex, mobile, email, a.note, b.id post_id, b.post_name, b.note post_note from t_user a, t_position b where a.position_id = b.id </select> <!-- <cache></cache> --> <resultMap id="BaseResultMap" type="TUser"> <!-- <constructor> <idArg column="id" javaType="int"/> <arg column="userName" javaType="String"/> </constructor> --> <id column="id" property="id" /> <result column="userName" property="userName" /> <result column="realName" property="realName" /> <result column="sex" property="sex" /> <result column="mobile" property="mobile" /> <result column="email" property="email" /> <result column="note" property="note" /> </resultMap> <sql id="Base_Column_List"> id, userName, realName, sex, mobile, email, note, position_id </sql> <resultMap id="userAndPosition1" extends="BaseResultMap" type="TUser"> <association property="position" javaType="TPosition" columnPrefix="post_" > <id column="id" property="id"/> <result column="name" property="postName"/> <result column="note" property="note"/> </association> </resultMap> <resultMap id="userAndPosition2" extends="BaseResultMap" type="TUser"> <association property="position" fetchType="lazy" column="position_id" select="com.enjoylearning.mybatis.mapper.TPositionMapper.selectByPrimaryKey" /> </resultMap> <select id="selectUserPosition1" resultMap="userAndPosition1" > select a.id, userName, realName, sex, mobile, email, a.note, b.id post_id, b.post_name, b.note post_note from t_user a, t_position b where a.position_id = b.id </select> <select id="selectUserPosition2" resultMap="userAndPosition2" > select a.id, a.userName, a.realName, a.sex, a.mobile, a.position_id from t_user a </select> <resultMap id="userAndJobs1" extends="BaseResultMap" type="TUser"> <collection property="jobs" ofType="com.enjoylearning.mybatis.entity.TJobHistory" > <result column="comp_name" property="compName" jdbcType="VARCHAR" /> <result column="years" property="years" jdbcType="INTEGER" /> <result column="title" property="title" jdbcType="VARCHAR" /> </collection> </resultMap> <resultMap id="userAndJobs2" extends="BaseResultMap" type="TUser"> <collection property="jobs" fetchType="lazy" column="id" select="com.enjoylearning.mybatis.mapper.TJobHistoryMapper.selectByUserId" /> </resultMap> <select id="selectUserJobs1" resultMap="userAndJobs1"> select a.id, a.userName, a.realName, a.sex, a.mobile, b.comp_name, b.years, b.title from t_user a, t_job_history b where a.id = b.user_id </select> <select id="selectUserJobs2" resultMap="userAndJobs2"> select a.id, a.userName, a.realName, a.sex, a.mobile from t_user a </select> <resultMap id="userAndHealthReportMale" extends="userAndHealthReport" type="TUser"> <collection property="healthReports" column="id" select= "com.enjoylearning.mybatis.mapper.THealthReportMaleMapper.selectByUserId"></collection> </resultMap> <resultMap id="userAndHealthReportFemale" extends="userAndHealthReport" type="TUser"> <collection property="healthReports" column="id" select= "com.enjoylearning.mybatis.mapper.THealthReportFemaleMapper.selectByUserId"></collection> </resultMap> <resultMap id="userAndHealthReport" extends="BaseResultMap" type="TUser"> <discriminator column="sex" javaType="int"> <case value="1" resultMap="userAndHealthReportMale"/> <case value="2" resultMap="userAndHealthReportFemale"/> </discriminator> </resultMap> <select id="selectUserHealthReport" resultMap="userAndHealthReport"> select <include refid="Base_Column_List" /> from t_user a </select> <resultMap type="TUser" id="userRoleInfo" extends="BaseResultMap"> <collection property="roles" ofType="TRole" columnPrefix="role_"> <result column="id" property="id" /> <result column="Name" property="roleName" /> <result column="note" property="note" /> </collection> </resultMap> <select id="selectUserRole" resultMap="userRoleInfo"> select a.id, a.userName, a.realName, a.sex, a.mobile, a.note, b.role_id, c.role_name, c.note role_note from t_user a, t_user_role b, t_role c where a.id = b.user_id AND b.role_id = c.id </select> <select id="selectUserByRoleId" resultMap="userRoleInfo"> select <include refid="Base_Column_List" /> from t_user a, t_user_role b where a.id = b.user_id and b.role_id = #{id} </select> <select id="selectByEmailAndSex1" resultMap="BaseResultMap" parameterType="map"> select <include refid="Base_Column_List" /> from t_user a where a.email like CONCAT('%', #{email}, '%') and a.sex =#{sex} </select> <select id="selectByEmailAndSex2" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from t_user a where a.email like CONCAT('%', #{email}, '%') and a.sex = #{sex} </select> <select id="selectByEmailAndSex3" resultMap="BaseResultMap" parameterType="com.enjoylearning.mybatis.entity.EmailSexBean"> select <include refid="Base_Column_List" /> from t_user a where a.email like CONCAT('%', #{email}, '%') and a.sex = #{sex} </select> <select id="selectBySymbol" resultMap="BaseResultMap"> select ${inCol} from ${tableName} a where a.userName = #{userName} order by ${orderStr} </select> <select id="selectIfOper" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from t_user a where 1=1 <if test="email != null and email != ''"> and a.email like CONCAT('%', #{email}, '%') </if> <if test="sex != null "> and a.sex = #{sex} </if> </select> <select id="selectIfandWhereOper" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from t_user a <where> <if test="email != null and email != ''"> and a.email like CONCAT('%', #{email}, '%') </if> <if test="sex != null "> and a.sex = #{sex} </if> </where> </select> <select id="selectChooseOper" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from t_user a <where> <choose> <when test="email != null and email != ''"> and a.email like CONCAT('%', #{email}, '%') </when> <when test="sex != null"> and a.sex = #{sex} </when> <otherwise> and 1=1 </otherwise> </choose> </where> </select> <update id="updateIfOper" parameterType="TUser"> update t_user set <if test="userName != null"> userName = #{userName,jdbcType=VARCHAR}, </if> <if test="realName != null"> realName = #{realName,jdbcType=VARCHAR}, </if> <if test="sex != null"> sex = #{sex,jdbcType=TINYINT}, </if> <if test="mobile != null"> mobile = #{mobile,jdbcType=VARCHAR}, </if> <if test="email != null"> email = #{email,jdbcType=VARCHAR}, </if> <if test="note != null"> note = #{note,jdbcType=VARCHAR} </if> where id = #{id,jdbcType=INTEGER} </update> <update id="updateIfAndSetOper" parameterType="TUser"> update t_user <set> <if test="userName != null"> userName = #{userName,jdbcType=VARCHAR}, </if> <if test="realName != null"> realName = #{realName,jdbcType=VARCHAR}, </if> <if test="sex != null"> sex = #{sex,jdbcType=TINYINT}, </if> <if test="mobile != null"> mobile = #{mobile,jdbcType=VARCHAR}, </if> <if test="email != null"> email = #{email,jdbcType=VARCHAR}, </if> <if test="note != null"> note = #{note,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=INTEGER} </update> <insert id="insertIfOper" parameterType="TUser"> insert into t_user ( <if test="id != null"> id, </if> <if test="userName != null"> userName, </if> <if test="realName != null"> realName, </if> <if test="sex != null"> sex, </if> <if test="mobile != null"> mobile, </if> <if test="email != null"> email, </if> <if test="note != null"> note </if> ) values( <if test="id != null"> #{id,jdbcType=INTEGER}, </if> <if test="userName != null"> #{userName,jdbcType=VARCHAR}, </if> <if test="realName != null"> #{realName,jdbcType=VARCHAR}, </if> <if test="sex != null"> #{sex,jdbcType=TINYINT}, </if> <if test="mobile != null"> #{mobile,jdbcType=VARCHAR}, </if> <if test="email != null"> #{email,jdbcType=VARCHAR}, </if> <if test="note != null"> #{note,jdbcType=VARCHAR} </if> ) </insert> <select id="selectForeach4In" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from t_user a where a.userName in <foreach collection="array" open="(" close=")" item="userName" separator=","> #{userName} </foreach> </select> <insert id="insertForeach4Batch" useGeneratedKeys="true" keyProperty="id"> insert into t_user (userName, realName, sex, mobile,email,note, position_id) values <foreach collection="list" separator="," item="user"> ( #{user.userName,jdbcType=VARCHAR}, #{user.realName,jdbcType=VARCHAR}, #{user.sex,jdbcType=TINYINT}, #{user.mobile,jdbcType=VARCHAR}, #{user.email,jdbcType=VARCHAR}, #{user.note,jdbcType=VARCHAR}, #{user.position.id,jdbcType=INTEGER} ) </foreach> </insert> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer"> delete from t_user where id = #{id,jdbcType=INTEGER} </delete> <insert id="insert1" parameterType="TUser" useGeneratedKeys="true" keyProperty="id"> insert into t_user (id, userName, realName, sex, mobile, email, note, position_id) values (#{id,jdbcType=INTEGER}, #{userName,jdbcType=VARCHAR}, #{realName,jdbcType=VARCHAR}, #{sex,jdbcType=TINYINT}, #{mobile,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{note,jdbcType=VARCHAR}, #{position.id,jdbcType=INTEGER}) </insert> <insert id="insert2" parameterType="TUser"> <selectKey keyProperty="id" order="AFTER" resultType="int"> select LAST_INSERT_ID() </selectKey> insert into t_user (id, userName, realName, sex, mobile, email, note, position_id) values (#{id,jdbcType=INTEGER}, #{userName,jdbcType=VARCHAR}, #{realName,jdbcType=VARCHAR}, #{sex,jdbcType=TINYINT}, #{mobile,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{note,jdbcType=VARCHAR}, #{position.id,jdbcType=INTEGER}) </insert> <insert id="insertSelective" parameterType="TUser" useGeneratedKeys="true" keyProperty="id"> insert into t_user <trim prefix="(" suffix=")" suffixOverrides="," > <if test="id != null"> id, </if> <if test="userName != null"> userName, </if> <if test="realName != null"> realName, </if> <if test="sex != null"> sex, </if> <if test="mobile != null"> mobile, </if> <if test="email != null"> email, </if> <if test="note != null"> note, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null"> #{id,jdbcType=INTEGER}, </if> <if test="userName != null"> #{userName,jdbcType=VARCHAR}, </if> <if test="realName != null"> #{realName,jdbcType=VARCHAR}, </if> <if test="sex != null"> #{sex,jdbcType=TINYINT}, </if> <if test="mobile != null"> #{mobile,jdbcType=VARCHAR}, </if> <if test="email != null"> #{email,jdbcType=VARCHAR}, </if> <if test="note != null"> #{note,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="TUser"> update t_user <set> <if test="userName != null"> userName = #{userName,jdbcType=VARCHAR}, </if> <if test="realName != null"> realName = #{realName,jdbcType=VARCHAR}, </if> <if test="sex != null"> sex = #{sex,jdbcType=TINYINT}, </if> <if test="mobile != null"> mobile = #{mobile,jdbcType=VARCHAR}, </if> <if test="email != null"> email = #{email,jdbcType=VARCHAR}, </if> <if test="note != null"> note = #{note,jdbcType=VARCHAR}, </if> <if test="position != null"> position_id = #{position.id,jdbcType=INTEGER}, </if> </set> where id = #{id,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="TUser"> update t_user set userName = #{userName,jdbcType=VARCHAR}, realName = #{realName,jdbcType=VARCHAR}, sex = #{sex,jdbcType=TINYINT}, mobile = #{mobile,jdbcType=VARCHAR}, email = #{email,jdbcType=VARCHAR}, note = #{note,jdbcType=VARCHAR}, position_id = #{position.id,jdbcType=INTEGER} where id = #{id,jdbcType=INTEGER} </update> </mapper>
5、编写测试类
@Before public void init() throws IOException { //--------------------第一阶段--------------------------- // 1.读取mybatis配置文件创SqlSessionFactory String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); // 1.读取mybatis配置文件创SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); inputStream.close(); } @Test // 快速入门 public void quickStart() throws IOException { //--------------------第二阶段--------------------------- // 2.获取sqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // 3.获取对应mapper TUserMapper mapper = sqlSession.getMapper(TUserMapper.class); //--------------------第三阶段--------------------------- // 4.执行查询语句并返回单条数据 TUser user = mapper.selectByPrimaryKey(2); System.out.println(user); System.out.println("----------------------------------"); // 5.执行查询语句并返回多条数据 // List<TUser> users = mapper.selectAll(); // for (TUser tUser : users) { // System.out.println(tUser); // } }
执行测试类，结果如下：
mybatis开发的主要流程为
- 加入mybatis的依赖
- 添加mybatis的配置文件
- 编写实体类、mapper接口以及mapper xml文件；
- 编写实例代码
相比较传统JDBC而言，mybatis分层更加清晰，并且业务代码开发减少了50%左右，同时本身也支持数据库的连接的持久化。
