java数据库操作-cnblog

创建lib目录，填入jar包

选择

libraries添加lib目录

package nb;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;public class JDBCtest {private static final String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8";
//    private static final String url = "jdbc:mysql://localhost:3306/testweb?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8";private static final String username = "root";private static final String password = "";public static void main(String[] args) {try {Class.forName("com.mysql.cj.jdbc.Driver");Connection connection = DriverManager.getConnection(url,username,password);if (connection != null){System.out.println("数据库链接成功");}} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}
}

mysql -h localhost -u root -p

1 实现对数据库的取值

package nb;import java.sql.*;
import java.util.Date;
import java.util.concurrent.locks.StampedLock;public class JDBCtest {private static final String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8";private static final String username = "root";private static final String password = "";public static void main(String[] args) {try {Class.forName("com.mysql.cj.jdbc.Driver");Connection connection = DriverManager.getConnection(url, username, password);if (connection != null) {System.out.println("数据库链接成功");}String sql = "SELECT id,number,name,gender,createDt FROM student";Statement statement = connection.createStatement();ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()){int id = resultSet.getInt("id");System.out.println("id:"+id);int number = resultSet.getInt("number");System.out.println("number:"+number);String name = resultSet.getString("name");System.out.println("name:"+name);String gender = resultSet.getString("gender");System.out.println("gender:"+gender);Date createDt = resultSet.getDate("createDt");System.out.println("createDt:"+createDt);}} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}
}

2 注意在结尾关闭数据库

package nb;import java.sql.*;
import java.util.Date;
import java.util.concurrent.locks.StampedLock;public class JDBCtest {private static final String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8";private static final String username = "root";private static final String password = "";public static void main(String[] args) {try {Class.forName("com.mysql.cj.jdbc.Driver");Connection connection = DriverManager.getConnection(url, username, password);if (connection != null) {System.out.println("数据库链接成功");}String sql = "SELECT id,number,name,gender,createDt FROM student";Statement statement = connection.createStatement();ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()){int id = resultSet.getInt("id");System.out.println("id:"+id);int number = resultSet.getInt("number");System.out.println("number:"+number);String name = resultSet.getString("name");System.out.println("name:"+name);String gender = resultSet.getString("gender");System.out.println("gender:"+gender);Date createDt = resultSet.getDate("createDt");System.out.println("createDt:"+createDt);}resultSet.close();statement.close();connection.close();System.out.println("关闭数据库完成");} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}
}

3 对数据库进行模糊查询

package nb;import java.sql.*;
import java.util.Date;
import java.util.concurrent.locks.StampedLock;public class JDBCtest {private static final String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8";private static final String username = "root";private static final String password = "";public static void main(String[] args) {try {Class.forName("com.mysql.cj.jdbc.Driver");Connection connection = DriverManager.getConnection(url, username, password);if (connection != null) {System.out.println("数据库链接成功");}String sql = "SELECT * FROM student where name like '张%'";Statement statement = connection.createStatement();ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()){int id = resultSet.getInt("id");System.out.println("id:"+id);int number = resultSet.getInt("number");System.out.println("number:"+number);String name = resultSet.getString("name");System.out.println("name:"+name);String gender = resultSet.getString("gender");System.out.println("gender:"+gender);Date createDt = resultSet.getDate("createDt");System.out.println("createDt:"+createDt);}resultSet.close();statement.close();connection.close();System.out.println("关闭数据库完成");} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}
}

查询结果只有一个，关键变换位置在

String sql = “SELECT * FROM student where name like ‘张%’”;

数据库内部一共储存了两个值

4 实现对数据库的增删改查

package nb;import java.sql.*;
import java.util.Date;
import java.util.concurrent.locks.StampedLock;public class JDBCtest {private static final String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf8";private static final String username = "root";private static final String password = "";Connection connection;public void init_Connection(){//初始化链接数据库try {Class.forName("com.mysql.cj.jdbc.Driver");connection = DriverManager.getConnection(url, username, password);if (connection != null) {System.out.println("数据库链接成功");}} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}public void closeConnection(){ //关闭数据库if (connection != null){try {connection.close();System.out.println("数据库成功关闭");}catch (SQLException e){e.printStackTrace();}}}public void showAllData(){try {String sql = "SELECT * FROM student";Statement statement = connection.createStatement();ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()){int id = resultSet.getInt("id");System.out.println("id:"+id);int number = resultSet.getInt("number");System.out.println("number:"+number);String name = resultSet.getString("name");System.out.println("name:"+name);String gender = resultSet.getString("gender");System.out.println("gender:"+gender);}}catch(SQLException e){e.printStackTrace();}}public void add(int id,int number,String username,String gender){try {String sql = "insert into student values(?,?,?,?)";PreparedStatement statement = connection.prepareStatement(sql);statement.setInt(1,id);statement.setInt(2,number);statement.setString(3,username);statement.setString(4,gender);statement.executeUpdate();System.out.println("添加新同学成功");} catch (SQLException e) {e.printStackTrace();}}public void delete(int id){ //删除指定id的学生try {Statement statement = connection.createStatement();statement.executeUpdate("delete from student where id="+id);System.out.println("删除某成员成功");} catch (SQLException e) {e.printStackTrace();}}public void update(int id,String new_name){String sql = "update student set name = ? where id = ?";try {PreparedStatement preparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(2,id);preparedStatement.setString(1,new_name);preparedStatement.executeUpdate();} catch (SQLException e) {e.printStackTrace();}}public static void main(String[] args) {JDBCtest jdbCtest = new JDBCtest();jdbCtest.init_Connection();jdbCtest.add(4,12233,"王二","男");jdbCtest.delete(4);jdbCtest.update(3,"呃呃");jdbCtest.showAllData();jdbCtest.closeConnection();}
}