JDBC实现对服务器数据库的增删改查

 

1、options类

package MysqlOptions;

import java.sql.*;

public class options {
    public static final String url = "jdbc:mysql:/x.x.x.x:3306/mysql";//设置公网ip、端口和数据库
    public static final String user = "root";//用户名
    public static final String passwd = "******";//密码

    /*连接数据库*/
    public Connection getConnected(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver"); //加载JDBC驱动
            return  DriverManager.getConnection(url, user, passwd);
        }catch (SQLException | ClassNotFoundException e){
            System.out.println("connect error!");
            e.printStackTrace();
            return null;
        }
    }

    /*查询*/
    public ResultSet select(Connection conn,PreparedStatement pst){
        try {
            String op = "select * from student";
            pst = conn.prepareStatement(op);
            return pst.executeQuery();
        }catch (SQLException e){
            e.printStackTrace();
            return null;
        }
    }
    /*更新*/
    public int update(Connection conn,PreparedStatement pst){
        try{
            String op ="update student set name = 'wangqianqian' where name ='wqq'";
            pst = conn.prepareStatement(op);
            return pst.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
            return 0;
        }

    }

    /*插入*/
    public int insert(Connection conn,PreparedStatement pst){
        try{
            String op = "insert into student(name,score) value ('zhangsan','66')";
            pst = conn.prepareStatement(op);
            return pst.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
            return 0;
        }
    }

    /*删除*/
    public int delete(Connection conn,PreparedStatement pst){
        try{
            String op = "delete from student where name = 'zhangsan'";
            pst = conn.prepareStatement(op);
            return pst.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
            return 0;
        }
    }
    public void print(ResultSet rs){
        try {
            while (rs.next()) {
                String name = rs.getString(1);
                String score = rs.getString(2);
                System.out.println("name:" + name + ",score:" + score);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }


}

2、app类（main方法）

package MysqlOptions;

import java.sql.*;

public class app{
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        options sql = new options();
        conn = sql.getConnected();
        /*查询操作*/
        System.out.println("测试查询操作：");
        rs = sql.select(conn,pst);
        sql.print(rs);

        /*修改操作*/
        System.out.println("测试修改操作：");
        int i = sql.update(conn,pst);
        System.out.println("共修改了"+i+"条记录");

        rs = sql.select(conn,pst);//调用select方法再次查看更新过的表
        sql.print(rs);

        /*增加操作*/
        System.out.println("测试增加操作：");
        int j = sql.insert(conn,pst);
        System.out.println("共增加了"+j+"个表项");

        rs = sql.select(conn,pst);//调用select方法再次查看更新过的表
        sql.print(rs);

        /*删除操作*/
        System.out.println("测试删除操作：");
        int k = sql.delete(conn,pst);
        System.out.println("删除了"+k+"个表项");

        rs = sql.select(conn,pst);//调用select方法再次查看更新过的表
        sql.print(rs);


//        try {
//            rs.close();
//            pst.close();
//            conn.close();
//        }catch (SQLException e){
//            e.printStackTrace();
//        }

    }



}

PS:查看数据库data存放位置：

1、登录数据库

mysql -u root -p

2、 mysql命令中输入

show variables like ‘%data%’;