一、MySQL数据
建表语句
-- ----------------------------
-- Table structure for boys
-- ----------------------------
DROP TABLE IF EXISTS `boys`;
CREATE TABLE `boys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`boyName` varchar(20) DEFAULT NULL,
`userCP` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of boys
-- ----------------------------
INSERT INTO `boys` VALUES ('1', '张无忌', '100');
INSERT INTO `boys` VALUES ('2', '鹿晗', '800');
INSERT INTO `boys` VALUES ('3', '黄晓明', '50');
INSERT INTO `boys` VALUES ('4', '段誉', '300');
二、创建Boy实体类
package com.lenovo.jdbc;
//体现封装思想
//创建boy对应的实体类
public class Boy {
private int id;
private String boyName;
private int userCP;
@Override
public String toString() {
return "Boy{" +
"id=" + id +
", boyName='" + boyName + '\'' +
", userCP=" + userCP +
'}';
}
public Boy(){}//空参
public Boy(int id, String boyName, int userCP) {
this.id = id;
this.boyName = boyName;
this.userCP = userCP;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getBoyName() {
return boyName;
}
public void setBoyName(String boyName) {
this.boyName = boyName;
}
public int getUserCP() {
return userCP;
}
public void setUserCP(int userCP) {
this.userCP = userCP;
}
}
三、JDBC操作
1. 加载JDBC驱动,获取连接
public static Connection getConnection() {
//第一步 加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//第二步 获取连接
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://192.168.171.151:3306/emp?characterEncoding=utf-8", "root", "123456");
//输出连接
System.out.println(conn);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
2.查询
package com.lenovo.jdbc;
import org.datanucleus.store.rdbms.JDBCUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class selData {
public static Connection getConnection() {
//第一步 加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//第二步 获取连接
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://192.168.171.151:3306/emp?characterEncoding=utf-8", "root", "123456");
//输出连接
System.out.println(conn);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/*
* 查询
*/
//java调用mysql做查询操作
public List<Boy> selectAllBoys() {
//获取连接
Connection connection = getConnection();
String sql = "SELECT id,boyName,userCP FROM boys";
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
ArrayList<Boy> boys = null;
try {
//执行sql
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
//创建集合存放对象()
boys = new ArrayList<>();
//解析结果集
while (resultSet.next()){
int id = resultSet.getInt("id");
String boyName = resultSet.getString("boyName");
int userCP = resultSet.getInt("userCP");
//构造器
Boy boy = new Boy(id, boyName, userCP);
boys.add(boy);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return boys;
}
}
3. 增加
package com.lenovo.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class insertData {
public static Connection getConnection() {
//第一步 加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//第二步 获取连接
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://192.168.171.151:3306/emp?characterEncoding=utf-8", "root", "123456");
//输出连接
System.out.println(conn);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/*
* 增加
*/
public boolean addBoy(Boy boy){
//第一步 -> 获取连接
Connection connection = getConnection();
//第二步 -> 调用sql
String sql = "insert into boys (id,boyName,userCP) values (?,?,?) ";
PreparedStatement preparedStatement = null;
int i = 0;
try {
preparedStatement = connection.prepareStatement(sql);
//第三步 -> 赋值
preparedStatement.setInt(1,boy.getId());
preparedStatement.setString(2,boy.getBoyName());
preparedStatement.setInt(3,boy.getUserCP());
//第四步 ->执行
i = preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return i ==0? false : true;
}
}
4. 修改
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Updata {
public static Connection getConnection() {
//第一步 加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//第二步 获取连接
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://192.168.171.151:3306/emp?characterEncoding=utf-8", "root", "123456");
//输出连接
System.out.println(conn);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
/*
* 修改
*/
public static int update(Boy boy){
Connection connection = getConnection();
String sql = "update boys set BoyName=?,UserCP=? where id=? ";
PreparedStatement preparedStatement = null;
int i = 0;
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, boy.getBoyName());
preparedStatement.setInt(2, boy.getUserCP());
preparedStatement.setInt(3,boy.getId());
i = preparedStatement.executeUpdate();
System.out.println("成功");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("失败");
}finally {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return i;
}
}
5.删除
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class deleteClass {
public static Connection getConnection() {
//第一步 加载驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//第二步 获取连接
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:mysql://192.168.171.151:3306/emp?characterEncoding=utf-8", "root", "123456");
//输出连接
System.out.println(conn);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//删除
public static int delete(Boy boy) {
Connection connection = getConnection();
PreparedStatement preparedStatement = null;
//第二步 -> 调用sql
int i = 0;
try {
preparedStatement = connection.prepareStatement("delete from boys where id=?;");
preparedStatement.setInt(1,boy.getId());
//第三步 -> 赋值
i = preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
preparedStatement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return i;
}
}
6.测试
import com.lenovo.jdbc.*;
public class upTest {
public static void main(String[] args) {
Updata updata = new Updata();
Boy boy = new Boy();
//1. 查询测试
System.out.println("查询--------------------");
System.out.println(new selData().selectAllBoys());
//2. 增加
System.out.println("插入--------------------");
System.out.println(new insertData().addBoy(new Boy(1,"止庵",50)));
//3. 修改
System.out.println("修改--------------------");
boy.setBoyName("星星");
boy.setUserCP(20);
boy.setId(2);
int update = Updata.update(boy);
if(update != 0){
System.out.println("修改数据成功");
}
//4. 删除
System.out.println("删除--------------------");
boy.setId(3);
int delete = deleteClass.delete(boy);
if(delete != 0){
System.out.println("删除数据成功");
}
}
}
7.运行结果
![](https://i-blog.csdnimg.cn/blog_migrate/894df9a75f5a13f18d5105bd582792be.png)