jdbc访问数据库-修改

实例一：

目录结构：

mysql目录结构：

 

最初的student表：

代码实现：

Dbutil：

package util;

import java.sql.*;

public class Dbutil {
    static{
        try{
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        Connection connection = null;

        try {
            //2.建立连接
            //url的格式：主协议:子协议:名称
            String url = "jdbc:mysql://127.0.0.1:3306/mysql_test?useUnicode=true&characterEncoding=utf8";
            connection = DriverManager.getConnection(url,"root","13474501003");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    public static void closeConnection(Connection connection){
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    public static void closeStatement(PreparedStatement preparedStatement){
        if (preparedStatement != null){
            try {
                preparedStatement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}

Test1：

package demo2;

import util.Dbutil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class Test1 {
    public static void main(String[] args){
        Scanner in = new Scanner(System.in);
        System.out.println("请输入要修改的学号：");
        int sno = in.nextInt();
        System.out.println("请输入新的名字：");
        String name = in.next();
        System.out.println("请输入新地址：");
        String addr = in.next();
        System.out.println("请输入新年龄：");
        int age = in.nextInt();
        //
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        //
        connection = Dbutil.getConnection();
        try {
            String sql = "UPDATE student SET sname=?,addr=?,age=? WHERE sno=?";
            preparedStatement = connection.prepareStatement(sql);
            //设置参数
            preparedStatement.setString(1,name);
            preparedStatement.setString(2,addr);
            preparedStatement.setInt(3,age);
            preparedStatement.setInt(4,sno);
            //
            int n = preparedStatement.executeUpdate();
            //
            if (n > 0){
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            Dbutil.closeStatement(preparedStatement);
            Dbutil.closeConnection(connection);
        }
    }
}

 

结果：

 

 查询student表：

将sno=107的行修改为：name=贾赦，地址=上海，年龄=41

实例二：

目录结构：

 

mysql目录结构：

 

最初的student表：

代码实现：

Dbutil1：

package util;

import java.sql.*;

public class Dbuitl1 {

    static Connection connection = null;
    static PreparedStatement preparedStatement = null;
    static ResultSet resultSet = null;

    static{
        try{
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection(){
        try {
            //2.建立连接
            //url的格式：主协议:子协议:名称
            String url = "jdbc:mysql://127.0.0.1:3306/mysql_test?useUnicode=true&characterEncoding=utf8";
            connection = DriverManager.getConnection(url,"root","13474501003");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }
    //创建PreparedStatement对象，并设置参数
    public static void createPreparedStatement(String sql,Object...arr){
        try {
            preparedStatement = connection.prepareStatement(sql);
            //设置参数
            int i;
            for (i = 0;i < arr.length;i++){
                preparedStatement.setObject(i+1,arr[i]);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    //执行DML语句
    public static int executeUpdate(){
        int n = 0;
        try {
            n = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return n;
    }
    //执行查询语句，返回ResultSet对象
    public static ResultSet excuteQuery(){
        try {
            resultSet = preparedStatement.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return resultSet;
    }
    //关闭
    public static void close(){
        try {
            if (resultSet != null){
                resultSet.close();
            }
            if (preparedStatement != null){
                preparedStatement.close();
            }
            if (connection != null){
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

Test2：

package demo2;

import util.Dbuitl1;

public class Test2 {
    public static void main(String[] args){
        Dbuitl1.getConnection();
        String sql = "UPDATE student SET age=?,addr=? WHERE sno=?";
        Dbuitl1.createPreparedStatement(sql,44,"贾敬",107);
        //执行
        int n = Dbuitl1.executeUpdate();
        if (n > 0){
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }
        Dbuitl1.close();
    }
}

结果：

将sno=107的行修改为：age=44，addr=贾敬

修改后的student表：