Java访问MySQL数据库创建数据表并且对数据库数据进行增删改查

那年木兰花开

已于 2024-07-11 23:40:04 修改

阅读量2.4k

点赞数 1

分类专栏：数据库文章标签： java mysql idea

于 2022-04-10 14:02:09 首次发布

本文链接：https://blog.csdn.net/weixin_44355338/article/details/124076676

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本文详细介绍了如何使用Java连接MySQL数据库，包括创建数据表`bookinfor`，添加、删除、修改和查询数据的操作。在添加数据时，特别提到通过设置URL参数防止中文乱码问题。

摘要由CSDN通过智能技术生成

1、创建数据表

源代码如下:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class BookTable {//创建数据表的类
    public static void main(String[] args){
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookinformation","******","######");
            String sql = "create table bookinfor("+
                    "id varchar(255) primary key not null,"+
                    "bookname varchar(255) not null,"+
                    "booktype varchar(255) not null)";
            ps = con.prepareStatement(sql);
            ps.execute();
            ps.close();
            con.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

注释:"******"为数据库用户名,"######"为数据库密码，用户名和密码改成自己的数据库用户名和密码即可

创建的数据表名称为"bookinfor",字段分别有id,bookname和booktype,如图所示

2、添加数据

源代码如下:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class InsertData {
    public static void main(String[] args){
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookinformation?useUnicode=true&characterEncoding=utf8","******","######");
            String sql = "insert into bookinfor values(?,?,?);";
            ps = con.prepareStatement(sql);
            ps.setString(1,"01");
            ps.setString(2,"西游记");
            ps.setString(3,"神魔小说");
            ps.execute();
            ps.close();
            con.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

注释:在数据库后加上"?useUnicode=true&characterEncoding=utf8"是因为要防止向数据表中插入中文信息时出现乱码现象

数据库中数据如图所示

3、删除数据

源代码如下:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class DeleteData {
    public static void main(String[] args){
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookinformation","******","######");
            String sql = "delete from bookinfor where id = ?;";
            ps = con.prepareStatement(sql);
            ps.setString(1,"02");
            ps.execute();
            ps.close();
            con.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

删除前数据库数据:

删除后数据库数据:

4、修改数据

源代码如下:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class UpdateData {
    public static void main(String[] ags){
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookinformation?useUnicode=true&characterEncoding=utf8","******","######");
            String sql = "update bookinfor set bookname = ?,booktype = ? where id = ?;";
            ps = con.prepareStatement(sql);
            ps.setString(1,"红楼梦");
            ps.setString(2,"世情小说");
            ps.setString(3,"02");
            ps.execute();
            ps.close();
            con.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

修改前数据库数据:

修改后数据库数据:

5、查询数据库中全部数据

源代码如下:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class QueryData {
    public static void main(String[] args){
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookinformation","******","######");
            String sql = "select * from bookinfor;";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                System.out.println("编号:"+rs.getString(1)+"\t"+"书名:"+rs.getString(2)+"\t"+"类型:"+rs.getString(3));
            }
            rs.close();
            ps.close();
            con.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

数据库中数据:

控制台输出结果:

6、查询数据库中单条数据

源代码如下:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class QuerySingleData {
    public static void main(String[] args){
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bookinformation","******","######");
            String sql = "select * from bookinfor where id = ?;";
            ps = con.prepareStatement(sql);
            ps.setString(1,"01");
            rs = ps.executeQuery();
            while (rs.next()){
                System.out.println("编号:"+rs.getString(1)+"\t"+"书名:"+rs.getString(2)+"\t"+"类型:"+rs.getString(3));
            }
            rs.close();
            ps.close();
            con.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}

控制台输出结果如下: