【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】