java 基础做增删改查教学_Java Mybatis 增删改查 简单使用 入门 简版

一些基础问题的解决

数据库对应的表

初始数据

项目结构

pom.xml

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

com.example

test1

1.0-SNAPSHOT

jar

org.mybatis

mybatis

3.4.5

mysql

mysql-connector-java

5.1.6

log4j

log4j

1.2.12

junit

junit

4.10

User.java

package com.example.test1;

import java.io.Serializable;

import java.util.Date;

public class User implements Serializable {

private int id;

private String username;// 用户姓名

private String sex;// 性别

private Date birthday;// 生日

private String address;// 地址

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 getSex() {

return sex;

}

public void setSex(String sex) {

this.sex = sex;

}

public Date getBirthday() {

return birthday;

}

public void setBirthday(Date birthday) {

this.birthday = birthday;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

@Override

public String toString() {

return "User [id=" + id + ", username=" + username + ", sex=" + sex

+ ", birthday=" + birthday + ", address=" + address + "]";

}

}

log4j.properties

# Set root category priority to INFO and its only appender to CONSOLE.

#log4j.rootCategory=INFO, CONSOLE debug info warn error fatal

log4j.rootCategory=debug, CONSOLE, LOGFILE

# Set the enterprise logger category to FATAL and its only appender to CONSOLE.

log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE

# CONSOLE is set to be a ConsoleAppender using a PatternLayout.

log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender

log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout

log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

# LOGFILE is set to be a File appender using a PatternLayout.

log4j.appender.LOGFILE=org.apache.log4j.FileAppender

log4j.appender.LOGFILE.File=d:\axis.log

log4j.appender.LOGFILE.Append=true

log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout

log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

SqlMapConfig.xml

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

User.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

select * from user where id = #{v}

select * from user where username like CONCAT('%',#{username},'%')

select LAST_INSERT_ID()

insert into user (username,birthday,address,sex) values(#{username},#{birthday},#{address},#{sex})

update user

set username = #{username},sex = #{sex},birthday = #{birthday},address = #{address}

where id = #{id}

delete from user

where id = #{vvvvv}

MybatisTest.java

import com.example.test1.User;

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 org.junit.Test;

import java.io.IOException;

import java.io.InputStream;

import java.util.Date;

import java.util.List;

/**

* @ClassName: MybatisTest

* @author: benjamin

* @version: 1.0

* @description: TODO

* @createTime: 2019/07/13/11:50

*/

public class MybatisTest {

public static void main(String[] args) {

}

//通过Id查询一个用户

@Test

public void testSearchById() throws IOException {

//1.读取配置文件

InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");

//2.创建SqlSessionFactory工厂

SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

//3.使用工厂生产SqlSession对象

SqlSession session = sqlSessionFactory.openSession();

//4.执行Sql语句

User user = session.selectOne("User.findUserById", 1);

//5. 打印结果

System.out.println(user);

//6.释放资源

session.close();

in.close();

}

//根据用户名模糊查询用户列表

@Test

public void testFindUserByUsername() throws IOException {

//1.读取配置文件

InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");

//2.创建SqlSessionFactory工厂

SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

//3.使用工厂生产SqlSession对象

SqlSession session = sqlSessionFactory.openSession();

//4.执行Sql语句

List list = session.selectList("User.findUserByUsername", "测试");

//5. 打印结果

for (User user:list) {

System.out.println(user);

}

//6.释放资源

session.close();

in.close();

}

//添加用户

@Test

public void testInsertUser() throws IOException {

//1.读取配置文件

InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");

//2.创建SqlSessionFactory工厂

SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

//3.使用工厂生产SqlSession对象

SqlSession sqlSession = sqlSessionFactory.openSession();

//4.执行Sql语句

User user = new User();

user.setUsername("小强");

user.setBirthday(new Date());

user.setAddress("广州");

user.setSex("男");

int i = sqlSession.insert("User.insertUser", user);

sqlSession.commit();

//5. 打印结果

// 刚保存用户，此时用户ID需要返回。执行完上面insert程序后，此时就能知道用户的ID是多少

// 需要在User.xml文件中配置

System.out.println("插入id:"+user.getId());//插入id:30

//6.释放资源

sqlSession.close();

in.close();

}

//更新用户

@Test

public void testUpdateUserById() throws IOException {

//1.读取配置文件

InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");

//2.创建SqlSessionFactory工厂

SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

//3.使用工厂生产SqlSession对象

SqlSession sqlSession = sqlSessionFactory.openSession();

//4.执行Sql语句

User user = new User();

user.setId(3);

user.setUsername("小李");

user.setBirthday(new Date());

user.setAddress("深圳");

user.setSex("女");

int i = sqlSession.insert("User.updateUserById", user);

sqlSession.commit();

//5. 打印结果

System.out.println(user.getId());

//6.释放资源

sqlSession.close();

in.close();

}

//删除用户

@Test

public void testDeleteUserById() throws IOException {

//1.读取配置文件

InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");

//2.创建SqlSessionFactory工厂

SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

//3.使用工厂生产SqlSession对象

SqlSession sqlSession = sqlSessionFactory.openSession();

//4.执行Sql语句

int i = sqlSession.insert("User.deleteUserById", 3);

sqlSession.commit();

//5. 打印结果

System.out.println(i);

//6.释放资源

sqlSession.close();

in.close();

}

}