首先创建一个Poject,然后导入对应的JAR包
新建两个包,用来放实体类,测试类;再在src目录下新建一个配置文件mybatis-Config。(注,entity应该是pojo,博主为了省事,就没改。实体类第一个字母应该大写,博主也写错了。)
对应类和xml的配置情况
candy类
package com.nj.entity;
public class candy {
private int id;
private String name;
private String color;
private String taste;
private float price;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTaste() {
return taste;
}
public void setTaste(String taste) {
this.taste = taste;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
@Override
public String toString() {
return "candy [name=" + name + ", color=" + color + ", taste=" + taste + ", price=" + price + "]";
}
public String getColor() {
return color;
}
public void setColor(String color) {
this.color = color;
}
}
CandyMapper.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.nj.entity.candy">
<!-- 公共sql -->
<sql id="selectColumn">
id,name,color,taste,price
</sql>
<resultMap type="candy" id="candy">
<result column="id" property="id"/>
<result column="name" property="name"/>
<result column="color" property="color"/>
<result column="taste" property="taste"/>
<result column="price" property="price"/>
</resultMap>
<!-- 查询所有,使用include标签引用 -->
<select id="selectAllInfo" resultMap="candy">
select <include refid="selectColumn"></include> from candy
</select>
<!-- 参数类型为简单类型且参数单一 -->
<select id="selectByName" parameterType="java.lang.String" resultMap="candy">
select id,name,color,taste,price from candy where name=#{name}
</select>
<!-- 参数类型为多个时+模糊查询(使用字符串连接符||或者concat函数),转义字符小于"<",大于">" -->
<!-- 或者用<![CDATA[...]]>,省略号里是普通文本,不具备任何xml中的特殊符号 -->
<select id="selectByCondition" parameterType="java.util.Map" resultMap="candy">
select id,name,color,taste,price from candy where id<=#{id} and name like '%'|| #{name} ||'%'
</select>
<!-- 插入 -->
<insert id="insertCandy" parameterType="com.nj.entity.candy">
insert into candy(name,color,taste,price) values(#{name},#{color},#{taste},#{price});
</insert>
<!-- 批量删除,参数是数组,当参数只有一个数组的时候,参数类型可以不用指定-->
<delete id="deleteById">
delete from candy where id in
<!-- collection :集合对象:如果参数只有一个数组,那么值为arry,如果只有一个List,那么值为list
open:以什么开始
close:以什么结束
seperator:分隔符
-->
<foreach collection="array" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</delete>
</mapper>
TestMybatis.java
package com.nj.test;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.nj.entity.candy;
public class TestMybatis {
private static SqlSessionFactory sessionFacory;
static{
//1.新建工厂构建器
SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
//2.工厂构建器解析mybatis的config文件
sessionFacory=builder.build(TestMybatis.class.getClassLoader().getResourceAsStream("mybatis-Config.xml"));
}
@Test
public void selectAll() {
//3.拿SqlSession对象,这个对象相当于JDBC封装中的JDBCTemplate
SqlSession sqlSession=sessionFacory.openSession();
//4.查询所有
List<candy> list=sqlSession.selectList("com.nj.entity.candy.selectAllInfo");
//5.查询单个
candy cdy=sqlSession.selectOne("com.nj.entity.candy.selectByName", "white-candy");
System.out.println("selectOne--->"+cdy);
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i));
}
}
@Test
public void insertCandy(){
SqlSession sqlSession=sessionFacory.openSession();
candy cdy=new candy("black-candy","black","hava no taste",21);
sqlSession.insert("com.nj.entity.candy.insertCandy",cdy);
sqlSession.commit();
selectAll();
}
@Test
public void selectByCondition() {
SqlSession session=sessionFacory.openSession();
Map<Object, Object> map=new HashMap<>();
map.put("id", 3);
map.put("name", "bl");
List<candy> list=session.selectList("com.nj.entity.candy.selectByCondition",map);
for (candy cdy : list) {
System.out.println(cdy);
}
}
@Test
public void deleteById(){
SqlSession session =sessionFacory.openSession();
int[] ids=new int[]{3,4,5,6};
session.delete("com.nj.entity.candy.deleteById", ids);
session.commit();
}
}
mybatis-Config.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>
<!-- 别名配置 -->
<typeAliases>
<!-- 需要配置别名的包,别名默认为类名 -->
<package name="com.nj.entity"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<!-- 配置要连接的数据库信息 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis?useSSL=false&serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/nj/entity/CandyMapper.xml"/>
</mappers>
</configuration>
注意,如果是Mysql8.0,driver最好写com.mysql.cj.jdbc.Driver,虽然没有cj也能运行,但是会报警告,有些项目说不定就运行不了,还有就是url的配置,serverTimezone=UTC要写上,否则会报错,错误如下;useSSL可以不写,但是会报警告,最好也写上。
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: java.sql.SQLException: The server time zone value '̨±±±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
### The error may exist in com/nj/entity/CandyMapper.xml
### The error may involve com.nj.entity.candy.selectAllInfo
### The error occurred while executing a query
### Cause: java.sql.SQLException: The server time zone value '̨±±±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:107)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:94)
at com.nj.test.TestMybatis.selectAll(TestMybatis.java:26)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.sql.SQLException: The server time zone value '̨±±±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:76)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:832)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:208)
at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.doGetConnection(UnpooledDataSource.java:180)
at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.doGetConnection(UnpooledDataSource.java:175)
at org.apache.ibatis.datasource.unpooled.UnpooledDataSource.getConnection(UnpooledDataSource.java:79)
at org.apache.ibatis.datasource.pooled.PooledDataSource.popConnection(PooledDataSource.java:373)
at org.apache.ibatis.datasource.pooled.PooledDataSource.getConnection(PooledDataSource.java:82)
at org.apache.ibatis.transaction.jdbc.JdbcTransaction.openConnection(JdbcTransaction.java:131)
at org.apache.ibatis.transaction.jdbc.JdbcTransaction.getConnection(JdbcTransaction.java:58)
at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:271)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:69)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:56)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:259)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:132)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
... 26 more
Caused by: com.mysql.cj.exceptions.InvalidConnectionAttributeException: The server time zone value '̨±±±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:85)
at com.mysql.cj.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:128)
at com.mysql.cj.protocol.a.NativeProtocol.configureTimezone(NativeProtocol.java:2236)
at com.mysql.cj.protocol.a.NativeProtocol.initServerSession(NativeProtocol.java:2260)
at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:1314)
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:963)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:822)
... 46 more
建库语句:
create table candy(
id int(11) not null auto_increment,
name varchar(32) not null,
color varchar(32) not null,
taste varchar(32) not null,
price float(32,8) not null,
primary key (id)
)engine=MyISAM auto_increment=1 DEFAULT CHARSET=UTF8;
insert into candy(name,color,taste,price) values('white-candy','white','sweet',2);
insert into candy(name,color,taste,price) values('red-candy','red','old and sweet',2.5);