【demo】IntelliJ IDEA + MyBatis + SQL Server

 

【SQL Server】

#1-> 新建数据库【TestDB】

#1->1 新建表【Products】

插入测试数据

#1->2 新建表【UserInfo】

插入测试数据

【IntelliJ IDEA】新建Java项目，项目结构如下

【新建lib文件】添加相应的jar包

loj4j : https://logging.apache.org/log4j/2.x/

mssql-jdbc：https://docs.microsoft.com/zh-cn/sql/connect/jdbc/using-the-jdbc-driver?view=sql-server-2017

mybatis：http://www.mybatis.org/mybatis-3/zh/index.html

配置jar包依赖【File】->【Project Structure】-> 【Modules】->【Dependencies】

选择项目下的【lib】文件夹

【配置DataSource】

【View】->【Tool Windows】->【Database】新建【SQL Server（Microsoft）】链接

填写相应的名称、链接数据库的名字，用户名及密码，默认端口号1433

链接成功后

【JavaBean文件】对应数据库中的每张表结构单独写一个Bean文件，对记录进行封装

package com.mybatisdemo.beans;

public class ProductsBean {

    private int ProductID;
    private String ProductName;
    private double Price;
    private String ProductDescription;

    public ProductsBean() {
        super();
    }

    public ProductsBean(int productID, String productName, double price, String productDescription) {
        ProductID = productID;
        ProductName = productName;
        Price = price;
        ProductDescription = productDescription;
    }

    public ProductsBean(String productName, double price, String productDescription) {
        ProductName = productName;
        Price = price;
        ProductDescription = productDescription;
    }

    public int getProductID() {
        return ProductID;
    }

    public void setProductID(int productID) {
        ProductID = productID;
    }

    public String getProductName() {
        return ProductName;
    }

    public void setProductName(String productName) {
        ProductName = productName;
    }

    public double getPrice() {
        return Price;
    }

    public void setPrice(double price) {
        Price = price;
    }

    public String getProductDescription() {
        return ProductDescription;
    }

    public void setProductDescription(String productDescription) {
        ProductDescription = productDescription;
    }

    @Override
    public String toString() {
        return "ProductsBean{" +
                "ProductID=" + ProductID +
                ", ProductName='" + ProductName + '\'' +
                ", Price=" + Price +
                ", ProductDescription='" + ProductDescription + '\'' +
                '}';
    }
}

package com.mybatisdemo.beans;

import java.util.Date;

public class UserInfoBean {

    private int ID;
    private String username;
    private String birthdate;
    private String nationality;

    public UserInfoBean() {
        super();
    }

    public UserInfoBean(int id, String username, String birthdate, String nationality) {
        this.ID = id;
        this.username = username;
        this.birthdate = birthdate;
        this.nationality = nationality;
    }

    public UserInfoBean(String username, String birthdate, String nationality) {
        this.username = username;
        this.birthdate = birthdate;
        this.nationality = nationality;
    }

    public int getID() {
        return ID;
    }

    public void setID(int id) {
        this.ID = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getBirthdate() {
        return birthdate;
    }

    public void setBirthdate(String birthdate) {
        this.birthdate = birthdate;
    }

    public String getNationality() {
        return nationality;
    }

    public void setNationality(String nationality) {
        this.nationality = nationality;
    }

    @Override
    public String toString() {
        return "UserInfoBean{" +
                "id=" + ID +
                ", username='" + username + '\'' +
                ", birthdate=" + birthdate +
                ", nationality='" + nationality + '\'' +
                '}';
    }
}

【Mapper文件】写对应的映射文件，针对每一个表，有对应的接口文件和xml配置文件

【需要注意的地方】

#1-> 接口文件Interface仅仅定义数据库操作函数，不实现具体

#2-> Mapper.xml文件编写的时候需要注意以下几点：

     #2->1 namespace需要对应到相应的接口文件

<mapper namespace="com.mybatisdemo.mapper.ProductsMapper">

<mapper namespace="com.mybatisdemo.mapper.UserInfoMapper">

     #2->2 对应每一个Bean文件对应一个ResultMap，property对应Bean文件中的属性，column对应数据库中的字段

<resultMap id="ProductsDataMap" type="com.mybatisdemo.beans.ProductsBean">
        <id property="ProductID" column="ProductID" javaType="java.lang.Integer"></id>
        <result property="ProductName" column="ProductName" javaType="java.lang.String"></result>
        <result property="Price" column="Price" javaType="java.lang.Double"></result>
        <result property="ProductDescription" column="ProductDescription" javaType="java.lang.String"></result>
</resultMap>

    #2->3 insert语句中需要注意ID号自动增长与非自动增长的区别

<insert id="insertData" keyProperty="ProductID">
        insert into Products (ProductID, ProductName, Price, ProductDescription)
            values (#{ProductID},#{ProductName},#{Price},#{ProductDescription})
</insert>

<insert id="insertData" useGeneratedKeys="true" keyProperty="ID">
        insert into UserInfo (username, birthdate, nationality)
            values (#{username},#{birthdate},#{nationality})
</insert>

 

package com.mybatisdemo.mapper;

import com.mybatisdemo.beans.ProductsBean;

import java.util.ArrayList;

public interface ProductsMapper {

    //新增记录
    public int insertData(ProductsBean product) throws Exception;

    //更新记录
    public int updateData(ProductsBean product) throws Exception;

    //删除记录
    public void deleteData(int id) throws Exception;

    //根据ID查询记录
    public ProductsBean selectByID(int id) throws Exception;

    //查询所有记录
    public ArrayList<ProductsBean> selectAll() throws Exception;
}

注意：ProductsMapper.xml中的id号必须对应接口文件中的函数名称！ 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatisdemo.mapper.ProductsMapper">
    <!--自定义返回结果集-->

    <resultMap id="ProductsDataMap" type="com.mybatisdemo.beans.ProductsBean">
        <id property="ProductID" column="ProductID" javaType="java.lang.Integer"></id>
        <result property="ProductName" column="ProductName" javaType="java.lang.String"></result>
        <result property="Price" column="Price" javaType="java.lang.Double"></result>
        <result property="ProductDescription" column="ProductDescription" javaType="java.lang.String"></result>
    </resultMap>

    <!--在各种标签中的id属性必须和接口中的方法名相同，id属性值必须唯一的，不能重复使用。
        parameterType属性指明查询时使用的参数类型，resultType属性指明查询返回的结果集类型-->

    <!--useGeneratedKeys：（仅对insert有用）
        这会告诉MyBatis使用JDBC的getGeneratedKeys方法来取出数据
       （比如：像MySQL和SQLServer这样的数据库管理系统的自动递增字段）内部生成的主键。默认值：false。-->

    <!--keyProperty：（仅对 insert有用）
        标记一个属性，MyBatis会通过getGeneratedKeys或者通过insert语句的selectKey子元素设置它的值。默认：不设置。 -->

    <!--#{}中的内容，为占位符，当参数为某个JavaBean时，表示放置该Bean对象的属性值-->

    <insert id="insertData" keyProperty="ProductID">
        insert into Products (ProductID, ProductName, Price, ProductDescription)
            values (#{ProductID},#{ProductName},#{Price},#{ProductDescription})
    </insert>

    <update id="updateData" parameterType="com.mybatisdemo.beans.ProductsBean">
        update Products set ProductName = #{ProductName}, Price = #{Price},
            ProductDescription = #{ProductDescription} where ProductID = #{ProductID}
    </update>

    <delete id="deleteData" parameterType="int">
        delete from Products where ProductID = #{ProductID}
    </delete>

    <select id="selectByID" parameterType="int" resultMap="ProductsDataMap">
        select * from Products where ProductID = #{ProductID}
    </select>

    <select id="selectAll" resultMap="ProductsDataMap">
        select * from Products
    </select>

</mapper>

package com.mybatisdemo.mapper;

import com.mybatisdemo.beans.UserInfoBean;

import java.util.ArrayList;

public interface UserInfoMapper {

    //新增记录
    public int insertData(UserInfoBean product) throws Exception;

    //更新记录
    public int updateData(UserInfoBean product) throws Exception;

    //删除记录
    public void deleteData(int id) throws Exception;

    //根据ID查询记录
    public UserInfoBean selectByID(int id) throws Exception;

    //查询所有记录
    public ArrayList<UserInfoBean> selectAll() throws Exception;

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatisdemo.mapper.UserInfoMapper">
    <!--自定义返回结果集-->
    <resultMap id="UserInfoDataMap" type="com.mybatisdemo.beans.UserInfoBean">
        <id property="ID" column="ID" javaType="java.lang.Integer"></id>
        <result property="username" column="username" javaType="java.lang.String"></result>
        <result property="birthdate" column="birthdate" javaType="java.lang.String"></result>
        <result property="nationality" column="nationality" javaType="java.lang.String"></result>
    </resultMap>

    <!--在各种标签中的id属性必须和接口中的方法名相同，id属性值必须唯一的，不能重复使用。
        parameterType属性指明查询时使用的参数类型，resultType属性指明查询返回的结果集类型-->

    <!--useGeneratedKeys：（仅对insert有用）
        这会告诉MyBatis使用JDBC的getGeneratedKeys方法来取出数据
       （比如：像MySQL和SQLServer这样的数据库管理系统的自动递增字段）内部生成的主键。默认值：false。-->

    <!--keyProperty：（仅对 insert有用）
        标记一个属性，MyBatis会通过getGeneratedKeys或者通过insert语句的selectKey子元素设置它的值。默认：不设置。 -->

    <!--#{}中的内容，为占位符，当参数为某个JavaBean时，表示放置该Bean对象的属性值-->

    <insert id="insertData" useGeneratedKeys="true" keyProperty="ID">
        insert into UserInfo (username, birthdate, nationality)
            values (#{username},#{birthdate},#{nationality})
    </insert>

    <update id="updateData" parameterType="com.mybatisdemo.beans.UserInfoBean">
        update UserInfo set username = #{username}, birthdate = #{birthdate},
        nationality = #{nationality} where ID = #{ID}
    </update>

    <delete id="deleteData" parameterType="int">
        delete from UserInfo where ID = #{ID}
    </delete>

    <select id="selectByID" parameterType="int" resultMap="UserInfoDataMap">
        select * from UserInfo where ID = #{ID}
    </select>

    <select id="selectAll" resultMap="UserInfoDataMap">
        select * from UserInfo
    </select>
</mapper>

【mybatisconfg.xml】注册mapper文件

 

jdbc.driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url = jdbc:sqlserver://localhost:1433;useUnicode=true;databaseName=TestDB
jdbc.username = sa
jdbc.password = 180710

<?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="com/mybatisdemo/confg/sqlserver.properties"></properties>

    <!--设置SQL语句打印输出-->
    <settings>
        <setting name ="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <!--配置mybatis运行环境-->
    <environments default="mybatis">
        <environment id="mybatis">
            <!--type="JDBC"代表使用JDBC的提交和回滚来管理事务-->
            <transactionManager type="JDBC"></transactionManager>
            <!--mybatis提供了3种数据元类型，分别是：POOLEN、UPOOLED、JNDI-->
            <!--POOLED表示JDBC数据源连接池-->
            <!--UNPOOLED表示不支持数据源连接池-->
            <!--JDNI表示外部数据源连接池-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"></property>
                <property name="url" value="${jdbc.url}"></property>
                <property name="username" value="${jdbc.username}"></property>
                <property name="password" value="${jdbc.password}"></property>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!-- 告知映射文件方式1，自动扫描包内的Mapper接口与配置文件
            <package name="com/mybatis/mapper"/>-->
        <!-- 告知映射文件方式2，一个个配置 -->
        <mapper resource="com/mybatisdemo/mapper/ProductsMapper.xml"></mapper>
        <mapper resource="com/mybatisdemo/mapper/UserInfoMapper.xml"></mapper>
    </mappers>

</configuration>

【tools】新建SessionFactory类

package com.demo.tools;

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 java.io.Reader;

public class SessionFactory {

    public static SqlSessionFactory sessionFactory;

    static {
        try{
            //配置文件地址
            String resources = "com/demo/confg/mybatisconfg.xml";
            //使用MyBatis提供的Resources类加载mybatis的配置文件
            Reader reader = Resources.getResourceAsReader(resources);
            //构建sqlSession的工厂
            sessionFactory = new SqlSessionFactoryBuilder().build(reader);

            System.out.println("静态创建了一次SqlSessionFactory!");
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    //创建能执行映射文件中sql的sqlSession
    public static SqlSession getSession()
    {
        return sessionFactory.openSession();
    }
}

【service】对应bean文件新建数据操作类

需要注意的是：session和mapper的初始化都需要单独写在每个函数内

session = SessionFactory.getSession();
mapper = session.getMapper(ProductsMapper.class);

最开始是放在构造函数里面的，导致程序报错：Executor was closed.

分析原因：因为单独的函数里写了session.close() ，执行下一个函数的时候会发现session已经关闭的情况下又被强迫需要打开执行，导致程序报错！

package com.demo.service;

import com.demo.beans.ProductsBean;
import com.demo.mapper.ProductsMapper;
import com.demo.tools.SessionFactory;
import org.apache.ibatis.session.SqlSession;

import java.util.ArrayList;

public class ProductsDataService {

    private SqlSession session;
    private ProductsMapper mapper;

    public ProductsDataService() {
        super();
    }

    //新增记录
    public void insertData(ProductsBean data){
        
        session = SessionFactory.getSession();
        mapper = session.getMapper(ProductsMapper.class);
        
        try {
            mapper.insertData(data);
            System.out.println("[Products] 插入记录成功！");
            System.out.println(data.toString());
            session.commit();
            System.out.println("session commit!");
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
            System.out.println("session close!");
        }
    }

    //批量插入记录
    public void insertMultiData(ArrayList<ProductsBean> listBean){
        
        session = SessionFactory.getSession();
        mapper = session.getMapper(ProductsMapper.class);
        
        int count = 0;
        try {
            count = mapper.insertMultiData(listBean);
            session.commit();
            System.out.println("session commit!");
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
            System.out.println("session close!");
        }
        if(count>0){
            System.out.println("[Products] 多行插入记录成功！");
            System.out.println("影响数目： " + count);
        }
        else
            System.out.println("插入失败！" + count);
    }

    //更新记录
    public void updateData(ProductsBean data){
        
        session = SessionFactory.getSession();
        mapper = session.getMapper(ProductsMapper.class);

        try{
            mapper.updateData(data);
            System.out.println("[Products] 更新记录成功！");
            System.out.println(data.toString());
            session.commit();
            System.out.println("session commit!");
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
            System.out.println("session close!");
        }
    }

    //删除记录
    public void deleteData(int id){
        
        session = SessionFactory.getSession();
        mapper = session.getMapper(ProductsMapper.class);
        
        try{
            mapper.deleteData(id);
            System.out.println("[Products] 已经删除第"+id+"条记录！");
            session.commit();
            System.out.println("session commit!");
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
            System.out.println("session close!");
        }
    }

    //根据id查询记录
    public void selectDataById(int id){
        
        session = SessionFactory.getSession();
        mapper = session.getMapper(ProductsMapper.class);
        
        try{
            ProductsBean data = mapper.selectByID(id);
            System.out.println(data.toString());
            session.commit();
            System.out.println("session commit!");
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
            System.out.println("session close!");
        }
    }

    //查询所有的用户
    public void selectAll(){
        
        session = SessionFactory.getSession();
        mapper = session.getMapper(ProductsMapper.class);
        
        try{
            ArrayList<ProductsBean> listdata = mapper.selectAll();
            System.out.println("---------------Products selectAll--------------");
            System.out.println("ProductID\t ProductName\t Price\t ProductDescription");
            for (ProductsBean data:listdata)
            {
//                System.out.println(data.toString());
                System.out.println(data.getProductID()+"\t "+data.getProductName()+"\t "+data.getPrice()+"\t "+data.getProductDescription());
            }
            session.commit();
            System.out.println("session commit!");
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
            System.out.println("session close!");
        }
    }
}

package com.demo.service;

import com.demo.beans.UserInfoBean;
import com.demo.mapper.UserInfoMapper;
import com.demo.tools.SessionFactory;
import org.apache.ibatis.session.SqlSession;

import java.util.ArrayList;

public class UserInfoDataService {

    //新增记录
    public void insertData(UserInfoBean data){
        SqlSession session = SessionFactory.getSession();
        UserInfoMapper mapper = session.getMapper(UserInfoMapper.class);

        try {
            mapper.insertData(data);
            System.out.println("[UserInfo] 插入记录成功！");
            System.out.println(data.toString());
            session.commit();
            System.out.println("session commit!");
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
            System.out.println("session close!");
        }
    }

    //更新记录
    public void updateData(UserInfoBean data){
        SqlSession session = SessionFactory.getSession();
        UserInfoMapper mapper = session.getMapper(UserInfoMapper.class);

        try{
            mapper.updateData(data);
            System.out.println("[UserInfo] 更新记录成功！");
            System.out.println(data.toString());
            session.commit();
            System.out.println("session commit!");
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
            System.out.println("session close!");
        }
    }

    //删除记录
    public void deleteData(int id){
        SqlSession session = SessionFactory.getSession();
        UserInfoMapper mapper = session.getMapper(UserInfoMapper.class);

        try{
            mapper.deleteData(id);
            System.out.println("[UserInfo] 已经删除第"+id+"条记录！");
            session.commit();
            System.out.println("session commit!");
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
            System.out.println("session close!");
        }
    }

    //根据id查询记录
    public void selectDataById(int id){
        SqlSession session = SessionFactory.getSession();
        UserInfoMapper mapper = session.getMapper(UserInfoMapper.class);

        try{
            UserInfoBean data = mapper.selectByID(id);
            System.out.println(data.toString());
            session.commit();
            System.out.println("session commit!");
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
            System.out.println("session close!");
        }
    }

    //查询所有的用户
    public void selectAll(){
        SqlSession session = SessionFactory.getSession();
        UserInfoMapper mapper = session.getMapper(UserInfoMapper.class);
        try{
            ArrayList<UserInfoBean> listdata = mapper.selectAll();
            for (UserInfoBean data:listdata)
            {
                System.out.println(data.toString());
            }
            session.commit();
            System.out.println("session commit!");
        }catch (Exception e){
            e.printStackTrace();
            session.rollback();
        }finally {
            session.close();
            System.out.println("session close!");
        }
    }
}

 【测试类】main.java文件

package com.mybatisdemo.main;

import com.mybatisdemo.beans.ProductsBean;
import com.mybatisdemo.beans.UserInfoBean;
import com.mybatisdemo.service.ProductsDataService;
import com.mybatisdemo.service.UserInfoDataService;

public class Main {

    public static void main(String[] args) {
	// write your code here

        //生成一个dataservice类辅助数据操作
        ProductsDataService productsDataService = new ProductsDataService();

        //查询所有记录
        productsDataService.selectAll();

//        //插入一行新记录
//        ProductsBean product = new ProductsBean(2018,"fuyao",56.76,"同风而起，扶摇直上");
//        productsDataService.insertData(product);
//
//        productsDataService.selectAll();
//
//        //更新记录
//        product.setPrice(55.86);
//        product.setProductDescription("扶风摇曳，自如纵横");
//
//        productsDataService.updateData(product);
//
//        productsDataService.selectAll();
//
//        //通过id查询记录
//        productsDataService.selectDataById(2018);
//        //删除记录
//        productsDataService.deleteData(2018);
//
//        productsDataService.selectAll();


        //生成一个dataservice类辅助数据操作
        UserInfoDataService userInfoDataService = new UserInfoDataService();

        //查询所有记录
        userInfoDataService.selectAll();

//        //插入一条新记录
//        UserInfoBean userInfo = new UserInfoBean("彭于晏","1982-3-24","中国");
//        userInfoDataService.insertData(userInfo);
//
//        userInfoDataService.selectAll();
//
//        //更新记录
//        userInfo.setNationality("中国台湾");
//        userInfoDataService.updateData(userInfo);
//
//        userInfoDataService.selectDataById(8);
//        userInfoDataService.selectAll();
//
//        //删除记录
//        userInfoDataService.deleteData(8);
//
//        userInfoDataService.selectAll();

    }
}

 【日志log打印】

【src】目录下添加文件，如果不放在/src目录下会报错！

### 设置Logger输出级别和输出目的地 ###
log4j.rootLogger=debug,stdout,logfile

### 把日志信息输出到控制台 ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
#log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.SimpleLayout

### 把日志信息输出到文件：jbit.log ###
log4j.appender.logfile=org.apache.log4j.FileAppender
log4j.appender.logfile.File=jbit.log
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %F %p %m%n

###显示SQL语句部分
log4j.logger.com.ibatis=DEBUG
log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

<?xml version="1.0" encoding="UTF-8"?>

<Configuration status="OFF">
    <Appenders>
        <Console name="Console" target="SYSTEM_OUT">
            <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
        </Console>
    </Appenders>
    <Loggers>
        <Logger name="hive.server2.query.ApiQueryTest" level="trace">
            <AppenderRef ref="Console" />
        </Logger>
        <Logger name="hive.server2.query" level="debug">
            <AppenderRef ref="Console" />
        </Logger>
        <Root level="error">
            <AppenderRef ref="Console" />
        </Root>
    </Loggers>
</Configuration>

【output】