JDBC连接数据库实现增删改查功能

user数据库详解

模拟实现登录功能

package com.test;

import java.sql.*;

public class Login {
    public static void main(String[] args) {
        //用户输入用户名、密码。根据用户名去数据库查询数据，然后比对密码
        String name = "wang";
        String pwd = "0000";
        //加载驱动
        Connection conn = null;
        Statement statement = null;
        try {
            //连接数据库
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf8";
            conn = DriverManager.getConnection(url,"root","0000");
            //操作数据库语句 通过name去查询语句
            statement = conn.createStatement();
            String sql = "select * from user where username = '" + name + "'";
            //执行语句 查询操作
            ResultSet resultSet = statement.executeQuery(sql);
            //输出数据库信息
            String username = "";
            String password = "";
            //resultSet.next() 返回值 true false
            while(resultSet.next()){
                /*
                    getString()方法
                    getString("XXX")表示以 Java 编程语言中String的形式获取此 ResultSet对象的"XXX"行中指定列的值。
                    getString(0)表示以 Java 编程语言中String的形式获取此 ResultSet对象的第一行中指定列的值。
                */
                username = resultSet.getString("username");
                password = resultSet.getString("password");
                System.out.println("用户名:" + username);
                System.out.println("密码:" + password);
                if (password.equals(pwd)){
                    System.out.println("登录成功");
                }else {
                    System.out.println("登录失败");
                }
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

模拟使用登录功能 添加了preparedStatement语句

package com.test;

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

/**
 * PreparedStatement语句详解 事先对语句进行预处理，效率会高很多 推荐使用
 * 以预处理的方式
 */
public class ReLogin {
    public static void main(String[] args) throws Exception {
        //1.登录账号密码
        String name = "冯三";
        String pwd = "0000";
        //2.初始化
        Connection conn = null;
        try {
            //3.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //4.连接数据库
            String url = "jdbc:mysql://localhost:3306/hello";
            conn = DriverManager.getConnection(url,"root","0000");
            //5.设置数据库语句
            String sql = "select * from user where username = '冯三'";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                System.out.println("用户名："+resultSet.getString("username"));
                System.out.println("密码："+resultSet.getString("password"));
                System.out.println("性别："+resultSet.getString("sex"));
                System.out.println("年龄："+resultSet.getString("age"));
                System.out.println("用户登录成功");
            }else{
                System.out.println("用户登录失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            conn.close();
        }
    }
}

添加功能

package com.test;

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

public class InsertDemo {
    public static void main(String[] args) throws Exception {
        Connection conn = null;
        Statement statement = null;
        try {
            //1.加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.连接数据库
            String url = "jdbc:mysql://localhost:3306/hello";
            conn = DriverManager.getConnection(url,"root","0000");
            //2.设置语句
            statement = conn.createStatement();
            String sql = "insert into user (username,password,sex,age) values('冯三','123','男',32)";
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("插入成功");
            }else{
                System.out.println("插入失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            statement.close();
            conn.close();
        }
    }
}

删除功能

package com.test;

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

public class DeleteDemo {
    public static void main(String[] args) throws Exception {
        Connection conn = null;
        Statement statement = null;
        try {
            //1.加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.连接数据库
            String url = "jdbc:mysql://localhost:3306/hello";
            conn = DriverManager.getConnection(url,"root","0000");
            //3.加载语句
            statement = conn.createStatement();
            String sql = "delete from user where username = '冯三'";
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("删除成功");
            }else{
                System.out.println("删除失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            statement.close();
            conn.close();
        }

    }
}

修改功能·

package com.test;

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

public class UpdateDemo {
    public static void main(String[] args) throws Exception {
        Connection conn = null;
        Statement statement = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.连接数据库
            String url = "jdbc:mysql://localhost:3306/hello";
            conn = DriverManager.getConnection(url,"root","0000");
            //3.设置sql语句
            String sql = "update user set username = '冯三',password = '0000',sex = '男',age = 12 where username = '冯三'";
            statement = conn.createStatement();
            int i = statement.executeUpdate(sql);
            if (i > 0){
                System.out.println("修改成功");
            }else{
                System.out.println("修改失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            statement.close();
            conn.close();
        }
    }
}

查询功能

package com.test;

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

public class SelectDemo {
    public static void main(String[] args) throws Exception {
        Connection conn = null;
        Statement statement = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.连接数据库
            String url = "jdbc:mysql://localhost:3306/hello";
            conn = DriverManager.getConnection(url,"root","0000");
            //3.设置sql语句
            statement = conn.createStatement();
            String sql = "select * from user where username = '冯三'";
            ResultSet resultSet = statement.executeQuery(sql);
            if (resultSet.next()){
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String sex = resultSet.getString("sex");
                String age = resultSet.getString("age");
                System.out.println("用户名:"+username);
                System.out.println("密码:"+password);
                System.out.println("性别:"+sex);
                System.out.println("年龄:"+age);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            statement.close();
            conn.close();
        }

    }
}