建数据库sql
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (1, 'zhangsan', '8888888', '张三', 20, 'test1@itcast.cn');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (2, 'lisi', '123456', '李四', 20, 'test2@itcast.cn');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (3, 'wangwu', '123456', '王五', 28, 'test3@itcast.cn');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (4, 'zhaoliu', '123456', '赵六', 21, 'test4@itcast.cn');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (5, 'sunqi', '123456', '孙七', 24, 'test5@itcast.cn');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (6, 'caocao1', '123456', '曹操1', 301, '2@itcast.cn');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (9, 'Philips', '222', '1', NULL, '222@qq.com');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (10, 'name', 'edsfcsaFCsdf', '1', NULL, '111@qq.com');
INSERT INTO `test`.`tb_user`(`id`, `user_name`, `password`, `name`, `age`, `email`) VALUES (13, 'name', 'bjkcvnbjcvbn', 'name', NULL, 'update@qq.com');
User.java
package edu.cs.rg.air.monitor.entity;
import lombok.Data;
@Data
public class User {
private long id;
private String name;
private String email;
private String password;
public User() {
}
public User(String name, String email, String password) {
this.name = name;
this.email = email;
this.password = password;
}
public User(long id, String name, String email, String password) {
this.id = id;
this.name = name;
this.email = email;
this.password = password;
}
@Override
public String toString(){
return "User:(id-"+id+" name-"+name+" email-"+email+" password-"+password+" )";
}
}
项目结构:
JDBC链接数据库
package edu.cs.rg.air.monitor;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import edu.cs.rg.air.monitor.entity.User;
public class JdbcDemo {
public static void main(String[] args) throws ClassNotFoundException,SQLException {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法,加载驱动
//2.用户信息和url
String url="jdbc:mysql://localhost:3300/test";
String username = "root";
String password = "root";
//3.连接成功,连接数据库对象 Connection
Connection connection = DriverManager.getConnection(url,username,password);
//4.执行SQL对象 Statement
Statement statement = connection.createStatement();
//5.执行SQL可能存在的结果,查看返回的结果
String sql = "SELECT * FROM tb_user";
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,封装了查询出来的全部结果
List<User> luser = new ArrayList<User>();
User nUser = null;
while(resultSet.next()) {
nUser = new User();
nUser.setId((Long)resultSet.getObject("id"));
nUser.setName((String) resultSet.getObject("NAME"));
nUser.setEmail((String)resultSet.getObject("email"));
nUser.setPassword((String)resultSet.getObject("PASSWORD"));
luser.add(nUser);
// System.out.println("id="+resultSet.getObject("id"));
// System.out.println("name="+resultSet.getObject("NAME"));
// System.out.println("pwd="+resultSet.getObject("PASSWORD"));
// System.out.println("email="+resultSet.getObject("email"));
// System.out.println("birth="+resultSet.getObject("birthday"));
}
for(User user : luser){
System.out.println(user);
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
Mybatis 链接数据库
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>
<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://localhost:3300/test"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
<!-- <mapper class="edu.cs.rg.air.monitor.mapper.UserMapper"/> -->
</mappers>
</configuration>
UserMapper.java
package edu.cs.rg.air.monitor.mapper;
import java.util.List;
import edu.cs.rg.air.monitor.entity.User;
public interface UserMapper {
User selectUser(int id);
List<User> selectUsers();
List<User> selectUsersByFuzzy(String name);
int insertUser(User user);
int selectCount();
int deleteUser(long id);
int updateUser(User user);
}
UserMapper.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="edu.cs.rg.air.monitor.mapper.UserMapper">
<select id="selectUser" resultType="edu.cs.rg.air.monitor.entity.User">
select * from tb_user where id = #{id}
</select>
<select id="selectCount" resultType="int">
select COUNT(*) from tb_user
</select>
<select id="selectUsers" resultType="edu.cs.rg.air.monitor.entity.User">
select * from tb_user
</select>
<select id="selectUsersByFuzzy" parameterType="String" resultType="edu.cs.rg.air.monitor.entity.User">
select * from tb_user
where name like concat('%',#{name},'%')
</select>
<!-- (查询多个)
select * from tb_user
where
name like concat('%',"1",'%') or
user_name like concat('%',"zhangsan",'%')
-->
<!-- <insert id="insertUser" >
insert into tb_user(user_name,password,email) values('Philips','222','222@qq.com');
</insert> -->
<insert id="insertUser" parameterType="edu.cs.rg.air.monitor.entity.User">
insert into tb_user(user_name,password,email) values(#{name},#{password},#{email});
</insert>
<delete id="deleteUser">
delete from tb_user
where id= #{id}
</delete>
<update id="updateUser" parameterType="edu.cs.rg.air.monitor.entity.User">
update tb_user
set
name= #{name},
password= #{password},
email= #{email}
where id = #{id}
</update>
</mapper>
package edu.cs.rg.air.monitor;
import java.io.IOException;
import java.io.InputStream;
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;
import edu.cs.rg.air.monitor.entity.User;
import edu.cs.rg.air.monitor.mapper.UserMapper;
public class MyBatisDemo {
public static void main(String[] args){
String resource = "mybatis-config.xml";
try (InputStream inputStream = Resources.getResourceAsStream(resource)) {
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
try (SqlSession session = sqlSessionFactory.openSession()) {
// User user = (User) session.selectOne("edu.cs.rg.monitor.mapper.UserMapper.selectUser", 1);
UserMapper umapper = session.getMapper(UserMapper.class);
// User user = umapper.selectUser(1);
// if(user!=null){
// System.out.println("该用户信息2如下:");
// System.out.println(user);
// }else{
// System.out.println("没有该用户信息");
// }
User a = new User(13,"name","update@qq.com","bjkcvnbjcvbn");
// add User
// int count = umapper.insertUser(a);
// select Count from tb_user
int b = umapper.selectCount();
// System.out.println(b);
// int c = umapper.deleteUser(11);
// System.out.println(c);
int d = umapper.updateUser(a);
// List<User> users = umapper.selectUsers();
// User soutUser = null;
// for(User soutUser : users){
// System.out.println(soutUser.toString());
// }
List<User> users = umapper.selectUsersByFuzzy("1");
for(User soutUser : users){
System.out.println(soutUser.toString());
}
session.commit();
session.close();
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
使用springboot操作数据库
controller——>service——>mapper
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.5</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>edu.cs.rg.air</groupId>
<artifactId>monitor</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>monitor</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
UserController
package edu.cs.rg.air.monitor.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import edu.cs.rg.air.monitor.entity.User;
import edu.cs.rg.air.monitor.service.IUserService;
import edu.cs.rg.air.monitor.service.UserService;
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private IUserService userService;
@GetMapping("/getInfo/{ids}")
public String queryUserInfo(@PathVariable("ids")Integer id){
// public String queryUserInfo(Integer id){
String result = null;
// id = 1;
if(id != null && userService != null)
return userService.selectUser(id).toString();
else
return "用户id不存在";
}
// @ResponseBody @RequestBody Integer id
@GetMapping("/getAllInfo")
public String queryAllUserInfo(){
String result = null;
return userService.selectUsers().toString();
}
}
service
package edu.cs.rg.air.monitor.service;
import java.util.List;
import edu.cs.rg.air.monitor.entity.User;
public interface IUserService {
User selectUser(int id);
List<User> selectUsers();
List<User> selectUsersByFuzzy(String name);
int insertUser(User user);
int selectCount();
int deleteUser(long id);
int updateUser(User user);
}
package edu.cs.rg.air.monitor.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowire;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import edu.cs.rg.air.monitor.entity.User;
import edu.cs.rg.air.monitor.mapper.UserMapper;
@Service
public class UserService implements IUserService {
@Autowired
private UserMapper userMapper;
@Override
public User selectUser(int id) {
return userMapper.selectUser(id);
}
@Override
public List<User> selectUsers() {
return userMapper.selectUsers();
}
@Override
public List<User> selectUsersByFuzzy(String name) {
// TODO Auto-generated method stub
return null;
}
@Override
public int insertUser(User user) {
// TODO Auto-generated method stub
return 0;
}
@Override
public int selectCount() {
// TODO Auto-generated method stub
return 0;
}
@Override
public int deleteUser(long id) {
// TODO Auto-generated method stub
return 0;
}
@Override
public int updateUser(User user) {
// TODO Auto-generated method stub
return 0;
}
}
mapper
package edu.cs.rg.air.monitor.mapper;
import java.util.List;
import edu.cs.rg.air.monitor.entity.User;
public interface UserMapper {
User selectUser(int id);
List<User> selectUsers();
List<User> selectUsersByFuzzy(String name);
int insertUser(User user);
int selectCount();
int deleteUser(long id);
int updateUser(User user);
}
<?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="edu.cs.rg.air.monitor.mapper.UserMapper">
<select id="selectUser" resultType="edu.cs.rg.air.monitor.entity.User">
select * from tb_user where id = #{id}
</select>
<select id="selectCount" resultType="int">
select COUNT(*) from tb_user
</select>
<select id="selectUsers" resultType="edu.cs.rg.air.monitor.entity.User">
select * from tb_user
</select>
<select id="selectUsersByFuzzy" parameterType="String" resultType="edu.cs.rg.air.monitor.entity.User">
select * from tb_user
where name like concat('%',#{name},'%')
</select>
<!-- (查询多个)
select * from tb_user
where
name like concat('%',"1",'%') or
user_name like concat('%',"zhangsan",'%')
-->
<!-- <insert id="insertUser" >
insert into tb_user(user_name,password,email) values('Philips','222','222@qq.com');
</insert> -->
<insert id="insertUser" parameterType="edu.cs.rg.air.monitor.entity.User">
insert into tb_user(user_name,password,email) values(#{name},#{password},#{email});
</insert>
<delete id="deleteUser">
delete from tb_user
where id= #{id}
</delete>
<update id="updateUser" parameterType="edu.cs.rg.air.monitor.entity.User">
update tb_user
set
name= #{name},
password= #{password},
email= #{email}
where id = #{id}
</update>
</mapper>
代码下载
以上代码:
https://download.csdn.net/download/m0_61504367/85409418