9.9

jdbc与sql的交互

共7步骤
1.加载驱动
2.创建连接
3.写sql
4.得到statement对象
5.执行sql
6.处理结果集
7.关闭资源

创建数据库

创建表

在idea中加载驱动加入jar包
并开始连接sql

java增删改查

如果你的SQL 语句是诸如update,insert的更新语句，应该用statement的execute()方法，如果用的是statement的executeQuery()就会出现Can not issue data manipulation statements with executeQuery()的错误

增删改查代码
改

package com.zhongruan;

import java.security.KeyStore;
import java.sql.*;

public class test {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://localhost/zjgm?user=root&password=123456");
        System.out.println("成功");
        String sql="UPDATE stu SET age=1 where age=23";
        PreparedStatement statement=connection.prepareStatement(sql);
       statement.executeUpdate();
    }

增

package com.zhongruan;

import java.security.KeyStore;
import java.sql.*;

public class test {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://localhost/zjgm?user=root&password=123456");
        System.out.println("成功");
        String sql="insert into stu values(1,'sdsd',22)";
        PreparedStatement statement=connection.prepareStatement(sql);
       statement.executeUpdate();
    }

查

package com.zhongruan;

import java.security.KeyStore;
import java.sql.*;

public class test {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://localhost/zjgm?user=root&password=123456");
        System.out.println("成功");
        String sql="select * from stu";
        PreparedStatement statement=connection.prepareStatement(sql);
        ResultSet resultSet=statement.executeQuery();
        while (resultSet.next()){
            int id=resultSet.getInt(1);
            String name =resultSet.getString(2);
            int age=resultSet.getInt(3);
            System.out.println(id+name+age);
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

删

package com.zhongruan;

import java.security.KeyStore;
import java.sql.*;

public class test {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://localhost/zjgm?user=root&password=123456");
        System.out.println("成功");
        String sql="delete from stu where age =1";
        PreparedStatement statement=connection.prepareStatement(sql);
       statement.executeUpdate();
    }