JDBC连接数据库Mysql(附增删改查)
package com.example.java_algorithms;import com.mysql.cj.jdbc.Driver;import io.swagger.models.auth.In;import org.slf4j.LoggerFactory;import java.sql.*;import java.util.ArrayList;import java.util.HashMa
·
package com.example.java_algorithms;
import com.mysql.cj.jdbc.Driver;
import io.swagger.models.auth.In;
import org.slf4j.LoggerFactory;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.logging.Logger;
/*
SQL脚本:
CREATE DATABASE `monitor` DEFAULT CHARACTER SET utf8 ;
USE `monitor`;
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(200) DEFAULT NULL COMMENT 'QQ邮箱',
`password` varchar(50) DEFAULT NULL COMMENT '密码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
insert into `t_user`(`id`,`email`,`password`) values (1,'123456','1234564'),(2,'10000','141414'),(3,'小环','123456'),(4,'小环','123456');
*/
/**
* 连接数据库 导包:mysql-connector-java
*/
public class JDBC {
/**
* 连接数据库
* @return
*/
public static Connection connectMysql() {
/*
定义连接数据库需要的东西
*/
//定义url
String url="jdbc:mysql://localhost:3306/monitor?useSSL=true&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
//定义驱动
String driverName="com.mysql.cj.jdbc.Driver";
//用户名
String username="root";
//密码
String password="123456";
/*
1. 加载驱动
*/
try {
Class.forName(driverName);
//成功后,则连接数据库.输入url,用户名、密码
Connection connection=DriverManager.getConnection(url,username,password);
System.out.println("数据库连接成功!");
return connection;
} catch (ClassNotFoundException e) {
e.printStackTrace();//在命令行打印异常信息在程序中出错的位置及原因
System.out.println("数据库连接失败!");
}catch (SQLException e){
e.printStackTrace();
System.out.println("数据库连接失败!");
}
return null;
}
/**
* 数据库的增删改查语句
*/
/**
*查询所有
*/
static List<HashMap<String,Object>> selectAllUser(Connection connection){
String sql="SELECT id,email,password FROM t_user";
try {
//执行,预编译SQL语句
PreparedStatement preparedStatement=connection.prepareStatement(sql);
System.out.println("执行成功:");
//执行SQL语句
//定义接收的对象
ResultSet resultSet=preparedStatement.executeQuery(sql);
//定义数组接收遍历的对象,每一个元素类型--HashMap对象
List<HashMap<String,Object>> hashMapList=new ArrayList<>();
while (resultSet.next()){
HashMap<String,Object> hashMap=new HashMap<>();
hashMap.put("id",resultSet.getInt("id"));
hashMap.put("email",resultSet.getString("email"));
hashMap.put("password",resultSet.getString("password"));
hashMapList.add(hashMap);
}
return hashMapList;
}catch (SQLException e){
e.printStackTrace();
return null;
}
}
/**
* 增
*/
public static void addUser(Connection connection,HashMap<String,Object>hashMap){
String sql = "INSERT INTO t_user(email,password) VALUES(?,?)";
try {
PreparedStatement preparedStatement=connection.prepareStatement(sql);
preparedStatement.setString(1,hashMap.get("email").toString());
preparedStatement.setString(2,hashMap.get("password").toString());
int i=preparedStatement.executeUpdate();
if(i>0){
System.out.println("添加成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 删除
* @param
*
*/
public static void delete(Connection connection,String email){
String sql ="DELETE FROM t_user WHERE email=?";
/*
DELETE FROM t_user;删除所有
TRUNCATE t_user;(id重新自增)
*/
try {
PreparedStatement preparedStatement=connection.prepareStatement(sql);
preparedStatement.setString(1,email);
int i=preparedStatement.executeUpdate();
if(i>0) {
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 改
*/
public static void updateUser(Connection connection,String email,String password){
String sql="UPDATE t_user SET password=? WHERE email =?";
try {
PreparedStatement preparedStatement=connection.prepareStatement(sql);
preparedStatement.setString(1,password);
preparedStatement.setString(2,email);
int i=preparedStatement.executeUpdate();
if(i>0){
System.out.println("修改成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
//连接数据库
Connection connection=connectMysql();
//查询t_user表中的所有信息
List<HashMap<String,Object>> hashMapList=selectAllUser(connection);
System.out.println("查询所有:"+hashMapList);
//增加数据
HashMap<String,Object> addHashMap=new HashMap<>();
addHashMap.put("email","小红");
addHashMap.put("password","123456");
addUser(connection,addHashMap);
//删除
delete(connection,"小环");
//修改
updateUser(connection,"小红","123456789000");
}
}
更多推荐
已为社区贡献6条内容
所有评论(0)