mybatis基础方式的增删改查CRUD
lombok.jar 注解
一、Lombok构造方法注解的使用方法 Lombok提供了三个构造方法注解:
@NoArgsConstructor、@AllArgsConstructor和@RequiredArgsConstructor。
分别对应着无参构造方法、全参构造方法和有参构造方法
(根据类的成员变量自动生成参数)。
一、 基础方式的增删改查CRUD:
mybatis约定:
输入参数 parameterType 和 输出参数 resultType 在形式上 都只能有一个
如果输入,输出参数 : 是 简单类型 (8个基本类型 + String) , 则可以使用任何占位符。#{xxxxx}
如果是对象类型,则必须是对象的属性、#{属性名}
如果返回值类型是一个 对象 (如Student),则无论返回一个,还是多个,
在resultType都写成 org.lanqiao.entity.Student
即 resultType=“org.lanqiao.entity.Student”
注意事项:
a 如果使用的 事务方式为 JDBC,则需要手动commit提交,即session.commit();
b 所有的标签<select> <update>
<>等,都必须有SQL语句,但是SQL参数值可选
select * from student where stuno = #{xx}
sql有参数: session.insert(statement, 参数值 );
sql没参数: session.insert(statement);
Student.java
package org.lanqiao.entity;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.RequiredArgsConstructor;
import lombok.Setter;
import lombok.ToString;
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class Student {
private int stuNo;
private String stuName;
private int stuAge;
private String graName;
public int getStuNo() {
return stuNo;
}
public void setStuNo(int stuNo) {
this.stuNo = stuNo;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public int getStuAge() {
return stuAge;
}
public void setStuAge(int stuAge) {
this.stuAge = stuAge;
}
public String getGraName() {
return graName;
}
public void setGraName(String graName) {
this.graName = graName;
}
public Student() {
}
public Student(int stuNo, String stuName, int stuAge, String graName) {
super();
this.stuNo = stuNo;
this.stuName = stuName;
this.stuAge = stuAge;
this.graName = graName;
}
@Override
public String toString() {
return "Student [stuNo=" + stuNo + ", stuName=" + stuName + ", stuAge=" + stuAge + ", graName=" + graName + "]";
}
}
创建表 student
create table student(stuno NUMBER, stuname VARCHAR2(20), stuage NUMBER, graname VARCHAR2(20) );
alter table student add constraint student primary key (stuno);
INSERT into student VALUES(1, 'zs', 23, 'g1');
INSERT into student VALUES(2, 'ls', 24, 'g1');
commit;
conf.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>
<!-- 通过environments的default值 和 environment 的 id 来指定MyBatis运行时的数据库环境 -->
<environments default="development">
<!-- 开发环境(自己的计算机) -->
<environment id="development">
<!-- 事务提交方式
JDBC 利用JDBC方式处理事务(commit rollback close )
MANAGED 将事务交由其他组件去托管(spring, jobss), 默认情况会关闭连接。
如果不想关闭,使用以下设置
<transactionManager type="MANAGED" />
property name="closeConnection" value="false" />
-->
<transactionManager type="JDBC" />
<!-- 数据源类型
UNPOOLED: 传统的JDBC模式(每次访问数据库,均需要代开,关闭等数据库操作, 但是打开,关闭数据库是比较消耗性能的)
POOLED: 使用数据库
JNDI: 从Tomcat中获取一个内置的数据库连接池(数据库连接池=数据源)
-->
<dataSource type="POOLED">
<!-- 配置数据库信息 -->
<property name="driver" value="oracle.jdbc.OracleDriver" />
<property name="url"
value="jdbc:oracle:thin:@127.0.0.1:1521:ORCL" />
<property name="username" value="scott" />
<property name="password" value="tiger" />
</dataSource>
</environment>
<!-- 真正的项目应该在发布的那台计算机上运行 实施 -->
<environment id="shishi">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<!-- 配置数据库信息 -->
<property name="driver" value="oracle.jdbc.OracleDriver" />
<property name="url"
value="jdbc:oracle:thin:@127.0.0.2:1521:ORCL" />
<property name="username" value="scott" />
<property name="password" value="tiger" />
</dataSource>
</environment>
<!-- 测试 -->
<environment id="test">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<!-- 配置数据库信息 -->
<property name="driver" value="oracle.jdbc.OracleDriver" />
<property name="url"
value="jdbc:oracle:thin:@127.0.0.3:1521:ORCL" />
<property name="username" value="scott" />
<property name="password" value="tiger" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 加载映射文件 -->
<mapper resource="org/lanqiao/entity/studentMapper.xml" />
</mappers>
</configuration>
studentMapper.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">
<!-- namespace : 该mapper.xml映射文件的唯一标识 -->
<mapper namespace="org.lanqiao.entity.studentMapper">
<!-- 后续通过 namespace.id -->
<!-- parameterType 输入参数的类型 -->
<!-- resultType 查询返回结果值的类型, 返回类型 -->
<select id="queryStudentByStuno" resultType="org.lanqiao.entity.Student" parameterType="int">
select * from student where stuno = #{stuno}
</select>
<insert id="addStudent" parameterType="org.lanqiao.entity.Student" >
insert into student(stuno, stuname, stuage, graname) values(#{stuNo},#{stuName},#{stuAge},#{graName} )
</insert>
<update id="updateStudentByStuno" parameterType="org.lanqiao.entity.Student">
update student set stuname=#{stuName}, stuage=#{stuAge}, graname=#{graName} where stuno=#{stuNo}
</update>
<delete id="deleteStudentByStuno" parameterType="int">
delete from student where stuno = #{stuno}
</delete>
<select id="queryAllStudents" resultType="org.lanqiao.entity.Student">
select * from student
</select>
</mapper>
TestMybatis.java
package org.lanqiao.entity;
import java.io.IOException;
import java.io.Reader;
import java.util.Iterator;
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;
public class TestMybatis {
// 查询单个学生
public static void queryStudentByStuno() throws IOException {
// Connection - SqlSession操作MyBatis
// conf.xml - > reader
Reader reader = Resources.getResourceAsReader("conf.xml");
// reader -> SqlSession
// 可以通过build的第二个参数 指定数据库环境
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
// SqlSessionFactory connection
// 工厂模式
SqlSession session = sessionFactory.openSession();
String statement = "org.lanqiao.entity.studentMapper.queryStudentByStuno";
Student student = session.selectOne(statement, 1);
System.out.println(student);
session.close();
}
// 查询全部学生
public static void queryAllStudents() throws IOException {
// Connection - SqlSession操作MyBatis
// conf.xml - > reader
Reader reader = Resources.getResourceAsReader("conf.xml");
// reader -> SqlSession
// 可以通过build的第二个参数 指定数据库环境
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
// SqlSessionFactory connection
// 工厂模式
SqlSession session = sessionFactory.openSession();
String statement = "org.lanqiao.entity.studentMapper." + "queryAllStudents";
List<Student> students = session.selectList(statement);
for (Object student : students) {
System.out.println("student=" + student);
}
session.close();
}
// 增加学生
public static void addStudent() throws IOException {
// Connection - SqlSession操作MyBatis
// conf.xml - > reader
Reader reader = Resources.getResourceAsReader("conf.xml");
// reader -> SqlSession
// 可以通过build的第二个参数 指定数据库环境
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
// SqlSessionFactory connection
// 工厂模式
SqlSession session = sessionFactory.openSession();
String statement = "org.lanqiao.entity.studentMapper." + "addStudent";
Student student = new Student(3, "ww", 25, "s1");
int count = session.insert(statement, student); // statement:指定执行的SQL, student: SQL中需要的参数(???)
session.commit(); // 提交事务 JDBC 手动提交
System.out.println("增加" + count + "个学生");
session.close();
}
// 删除学生
public static void deleteStudentByStuno() throws IOException {
// Connection - SqlSession操作MyBatis
// conf.xml - > reader
Reader reader = Resources.getResourceAsReader("conf.xml");
// reader -> SqlSession
// 可以通过build的第二个参数 指定数据库环境
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
// SqlSessionFactory connection
// 工厂模式
SqlSession session = sessionFactory.openSession();
String statement = "org.lanqiao.entity.studentMapper." + "deleteStudentByStuno";
int count = session.delete(statement, 3);
session.commit(); // 提交事务 JDBC 手动提交
System.out.println("删除" + count + "个学生");
session.close();
}
// 修改学生
public static void updateStudentByStuno() throws IOException {
// Connection - SqlSession操作MyBatis
// conf.xml - > reader
Reader reader = Resources.getResourceAsReader("conf.xml");
// reader -> SqlSession
// 可以通过build的第二个参数 指定数据库环境
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader, "development");
// SqlSessionFactory connection
// 工厂模式
SqlSession session = sessionFactory.openSession();
String statement = "org.lanqiao.entity.studentMapper." + "updateStudentByStuno";
// 修改的参数
Student student = new Student();
// 修改那个人, where stuno = ?
student.setStuNo(2);
// 修改成什么样子?
student.setStuName("lxs");
student.setStuAge(45);
student.setGraName("s2");
// 执行
int count = session.update(statement, student);
session.commit(); // 提交事务 JDBC 手动提交
System.out.println("修改" + count + "个学生");
session.close();
}
public static void main(String[] args) throws IOException {
// queryStudentByStuno();
queryAllStudents();
// addStudent();
// deleteStudentByStuno();
updateStudentByStuno();
queryAllStudents();
}
}