java连接MySQL-优快云博客

本文链接：https://blog.youkuaiyun.com/may10/article/details/117219648

java连接数据库

1.导入jar包，在项目内创建libs目录，将jar包复制到libs目录下
在idea内右键libs目录选择 Add As Library

(加一步，创建jdbc连接类，写上main方法，以下代码都写在main方法中。如果有异常，在测试中直接抛出，具体些业务可以考虑try_catch)

2.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");

3.获取连接对象
Connection con = DriverManager.getConnection( "jdbc:mysql://IP地址:端口号/数据库名称?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT"
,账号,密码);

4.获取执行SQL的对象
Statement state = con.createStatement();

5.定义SQL语句

String str = "select * from 表格名称";

6.执行SQL语句

ResultSet resultSet = state.executeQuery(str);

7.返回结果操作

while (resultSet.next())

{

System.out.println("id: " + resultSet.getString(字段1)

+ " name: " + resultSet.getString(字段2) + " score: "

+ resultSet.getString(字段3) + " class: "

+ resultSet.getString(字段4));

}

更具实际情况打印，字段是String格式

8.释放资源
state.close();
con.close();

package Demo;

//数据库连接
//增删改查都已经测试过了
//语法上好像要求比SQLserver严格，注意细节。
//注意：查询一张表的信息就打印一次，不要连续查询两次，这样容易报错
/*例：
        查询1
        查询2
        输出1
        输出2
*/
//以上写法报错,改为：
/*
        查询1
        输出1
        查询2
        输出2
 */



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

public class Demo {

    public static void main(String[] args) throws Exception {

        //1.导入jar包，在项目内创建libs目录，将jar包复制到libs目录下
        //在idea内右键libs目录选择 Add As Library

        //2.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //3.获取连接对象
        Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/data?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT"
                ,"root","root");

        //4.定义SQL语句
        String str1 = "update student set score = score + 20 where id = '002'";
        String str2 = "select * from student";
        String str3 = "create table teacher (class varchar(20), name varchar(20), count int)";
        String str4 = "insert into teacher value ('计科1901', '李xx', '34')";
        String str5 = "insert into teacher value ('电商1802', '刘x', '48')";
        String str6 = "insert into teacher value ('物联网1703', 'fuck off', '54')";
        String str7 = "insert into teacher value ('法学1802', 'Mr.fucking', '63')";
        String str8 = "delete from student where id = '002'";
        String str9 = "insert into student value ('004', 'ddd', 66, '法学1802')";
        String str10 = "insert into student value ('005', 'eee', 56, '英语1902')";
        String str11 = "insert into student value ('002', 'bbb', 36, '电商1802')";
        String str12 = "select * from student inner join teacher on student.class = teacher.class";

        //5.获取执行SQL的对象
        Statement state = con.createStatement();

        //6.执行SQL语句
        //int count = state.executeUpdate(str1);

        //state.execute(str3);
        //state.execute(str4);
        //state.execute(str5);
        //state.execute(str6);
        //state.execute(str7);
        //state.execute(str8);
        //state.execute(str9);
        //state.execute(str10);
        //state.execute(str11);
        ResultSet resultSet1 = state.executeQuery(str2);


        //打印返回值
        //System.out.println(count);
        //System.out.println(resultSet);

        //读取结果集，必须读完
        while (resultSet1.next()){
            System.out.println("id: " + resultSet1.getString("id") + " name: "
                    + resultSet1.getString("name") + " score: "
                    + resultSet1.getString("score") + " class: "
                    + resultSet1.getString("class"));
        }

        //多表连接查询
        ResultSet resultSet2 = state.executeQuery(str12);

        while (resultSet2.next()){
            System.out.println("class: " + resultSet2.getString("student.class")
                    + " sName: " + resultSet2.getString("student.name")
                    + " tName: " + resultSet2.getString("teacher.name"));
        }


        //7.释放资源
        state.close();
        con.close();
    }

}

1.删除数据库

DROP DATABASE 数据库名称

2.创建数据库

CREATE DATABASE 数据库名称

(创建了数据库后，需要使用数据库：use 数据库名称 )

3.创建表格（设置主键自增）

CREATE TABLE 表格名称 (
id INT AUTO_INCREMENT,
PRIMARY KEY (id),
na VARCHAR (20),
phone VARCHAR (20),
address VARCHAR (50)
)

红色部分是设置 id 为主键，并且添加自增，如不需要可以修改为

只要主键：

id INT，

PRIMARY KEY (id),

注：需要自增必须设置为主键