JDBC
发表于
2021-03-24
|
更新于
2021-03-24
总字数:
3k |
阅读时长:
13分钟
|
阅读量:
JDBC 通过 JDBC 连接 MySQL 数据库 前提条件
1、jar包:连接驱动包 【mysql-connector-java-5.1.47.jar】 2、url:确定连接的数据库所在网络地址和对应操作哪一个数据库,由协议名+子协议名+数据源名构成,即 jdbc:mysql://主机地址:端口号/数据库名 【例】jdbc:mysql://localhost:3306/islu 3、user:用户名 【root】 4、password:密码 【root】
步骤
1、加载驱动 2、准备必要的连接参数 3、获取数据库连接 4、关闭资源!!!
案例代码【测试连接】
package JDBC;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class Demo1 { public static void main (String[] args) { Connection connection = null ; try { Class.forName("com.mysql.jdbc.Driver" ); String url = "jdbc:mysql://localhost:3306/islu?useSSL=true&characterEncoding=utf8" ; String user = "root" ; String password = "root" ; connection = DriverManager.getConnection(url, user, password); System.out.println(connection); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } finally { try { connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
【注意】连接数据库可以配置一些相关的参数,数据库名与参数之间用 ? 进行分隔,参数之间通过 & 分隔,常用参数及格式如下
数据库驱动加载过程
public class Driver extends NonRegisteringDriver implements java .sql.Driver { static { try { java.sql.DriverManager.registerDriver(new Driver ()); } catch (SQLException E) { throw new RuntimeException ("Can't register driver!" ); } } public Driver () throws SQLException { } }
【注意】后续会用到静态代码块去完成一些初始化的操作
JDBC 核心 API
class java .sql.DriverManagerstatic java.sql.Connection getConnection (String url, String user, String password) ;interface java .sql.Connectionjava.sql.Statement createStatement () ; java.sql.PreparedStatement prepareStatement (String sql) ; interface java .sql.Statementint executeUpdate (String sql) ;java.sql.ResultSet executeQuery (String sql) ; public interface java .sql.PreparedStatement extends java .sql.Statementint executeUpdate () ;java.sql.ResultSet executeQuery () ; setXXX(int index, XXX value) interface java .sql.ResultSetXXX getXXX (int columnIndex) ; XXX getXXX (String columnLabel) ; boolean next () ;
Statement 操作 SQL 语句 增删改操作步骤【重点】
1、加载驱动 2、准备连接数据库所需要的参数 3、获取数据库连接 4、获取Statement搬运工对象 5、准备SQL语句 6、执行SQL语句获取受影响的行数
Statement 插入 SQL 数据操作
package JDBC;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class Demo2 { public static void main (String[] args) { Connection connection = null ; Statement statement = null ; try { Class.forName("com.mysql.jdbc.Driver" ); String url = "jdbc:mysql://localhost:3306/islu?useSSL=true&characterEncoding=utf8" ; String user = "root" ; String password = "root" ; connection = DriverManager.getConnection(url, user, password); statement = connection.createStatement(); String sql = "insert into user(uname,upassword) values('islu','islu.cn');" ; int affectedRows = statement.executeUpdate(sql); System.out.println("受影响的行数" + affectedRows); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } finally { try { if (statement != null ) { statement.close(); } if (connection != null ) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } } }
Statement 修改 SQL 数据操作
package JDBC;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class Demo3 { public static void main (String[] args) { Connection connection=null ; Statement statement=null ; try { Class.forName("com.mysql.jdbc.Driver" ); String url="jdbc:mysql://localhost:3306/islu?useSSL=true&characterEncoding=utf8" ; String user="root" ; String password="root" ; connection = DriverManager.getConnection(url, user, password); statement = connection.createStatement(); String sql="update user set uname = '彭于晏' where uid = 1;" ; int affectedRows = statement.executeUpdate(sql); System.out.println(affectedRows); } catch (ClassNotFoundException| SQLException e) { e.printStackTrace(); }finally { try { if (connection!=null ){ connection.close(); } if (statement!=null ){ statement.close(); } } catch (SQLException e) { e.printStackTrace(); } } } }
Statement 删除 SQL 数据操作
public class Demo4 { public static void main (String[] args) { Connection connection = null ; Statement statement = null ; try { Class.forName("com.mysql.jdbc.Driver" ); String url = "jdbc:mysql://localhost:3306/FC2020?useSSL=true&characterEncoding=utf8" ; String user = "root" ; String password = "root" ; connection = DriverManager.getConnection(url, user, password); statement = connection.createStatement(); String sql = "delete from student where id = 1;" ; int affectedRows = statement.executeUpdate(sql); System.out.println("受影响的行数:" + affectedRows); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } finally { try { if (statement != null ) { statement.close(); } if (connection != null ) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } } }
查询操作步骤【重点】
1、加载驱动 2、准备连接数据库所需要的参数 3、获取数据库连接 4、获取Statement搬运工对象 5、准备SQL语句 6、执行SQL语句获取结果集对象 7、判断结果集对象中是否有数据 8、如果结果集对象中存在数据,获取每个数据库字段对应类型的数据
Statement 查询 SQL 数据操作 准备实体类
package bean;public class User { private int uid; private String uname; private String upassword; public User () { } public User (int uid, String uname, String upassword) { this .uid = uid; this .uname = uname; this .upassword = upassword; } public int getUid () { return uid; } public void setUid (int uid) { this .uid = uid; } public String getUname () { return uname; } public void setUname (String uname) { this .uname = uname; } public String getUpassword () { return upassword; } public void setUpassword (String upassword) { this .upassword = upassword; } @Override public String toString () { return "User{" + "uid=" + uid + ", uname='" + uname + '\'' + ", upassword='" + upassword + '\'' + '}' ; } }
【注意】根据阿里巴巴开发手册,实体类成员变量要用包装类!!!
查询单行
package JDBC;import bean.User;import java.sql.*;public class Demo5 { public static void main (String[] args) { Connection connection = null ; Statement statement = null ; ResultSet resultSet = null ; try { Class.forName("com.mysql.jdbc.Driver" ); String url = "jdbc:mysql://localhost:3306/islu?useSSL=true&characterEncoding=UTF8" ; String user = "root" ; String password = "root" ; connection = DriverManager.getConnection(url, user, password); statement = connection.createStatement(); String sql = "select * from user where uid = 1;" ; resultSet = statement.executeQuery(sql); User userinfo = null ; while (resultSet.next()) { int uid = resultSet.getInt(1 ); String uname = resultSet.getString(2 ); String upassword = resultSet.getString(3 ); userinfo = new User (uid, uname, upassword); } System.out.println(userinfo); } catch (SQLException | ClassNotFoundException e) { e.printStackTrace(); } finally { try { if (resultSet != null ) { resultSet.close(); } if (statement != null ) { statement.close(); } if (connection != null ) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } } }
查询多行
package JDBC;import bean.User;import java.sql.*;import java.util.ArrayList;public class Demo6 { public static void main (String[] args) { Connection connection = null ; Statement statement = null ; ResultSet resultSet = null ; try { Class.forName("com.mysql.jdbc.Driver" ); String url = "jdbc:mysql://localhost:3306/islu?useSSL=true&characterEncoding=UTF8" ; String user = "root" ; String password = "luhuijun" ; connection = DriverManager.getConnection(url, user, password); statement = connection.createStatement(); String sql = "select * from user;" ; resultSet = statement.executeQuery(sql); User userinfo = null ; ArrayList<User> list=new ArrayList <>(); while (resultSet.next()) { int uid = resultSet.getInt(1 ); String uname = resultSet.getString(2 ); String upassword = resultSet.getString(3 ); userinfo = new User (uid, uname, upassword); list.add(userinfo); } System.out.println(list); } catch (SQLException | ClassNotFoundException e) { e.printStackTrace(); } finally { try { if (resultSet != null ) { resultSet.close(); } if (statement != null ) { statement.close(); } if (connection != null ) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } } }
常见数据库连接
-------------------------------oracle------------------ 驱动:oracle.jdbc.driver.OracleDriver URL:jdbc:oracle:thin:@machine_name:port:dbname 注:machine_name:数据库所在的机器的名称; port:端口号,默认是1521 -------------------------------mysql------------------- 驱动:com.mysql.jdbc.Driver URL:jdbc:mysql://machine_name:port/dbname 注:machine_name:数据库所在的机器的名称; port:端口号,默认3306 ------------------------------SQL Server--------------- 驱动:com.microsoft.jdbc.sqlserver.SQLServerDriver URL:jdbc:microsoft:sqlserver://<:port>;DatabaseName= 注:machine_name:数据库所在的机器的名称; port:端口号,默认是1433 ------------------------------DB2---------------------- 驱动:com.ibm.db2.jdbc.app.DB2Driver URL:jdbc:db2://<:port>/dbname 注:machine_name:数据库所在的机器的名称; port:端口号,默认是5000 -------------------------------------------------------