中国建设银行笔试确认网站,85同城找工作招聘,公章在线制作网站,吴江做网站Java-JDBC操作MySQL 文章目录 Java-JDBC操作MySQL一、Java-JDBC-MySQL的关系二、创建连接三、登录MySQL四、操作数据库1、返回型操作2、无返回型操作 练习题目及完整代码 一、Java-JDBC-MySQL的关系 #mermaid-svg-B7qjXrosQaCOwRos {font-family:trebuchet ms,verd…Java-JDBC操作MySQL 文章目录 Java-JDBC操作MySQL一、Java-JDBC-MySQL的关系二、创建连接三、登录MySQL四、操作数据库1、返回型操作2、无返回型操作 练习题目及完整代码 一、Java-JDBC-MySQL的关系 #mermaid-svg-B7qjXrosQaCOwRos {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-B7qjXrosQaCOwRos .error-icon{fill:#552222;}#mermaid-svg-B7qjXrosQaCOwRos .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-B7qjXrosQaCOwRos .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-B7qjXrosQaCOwRos .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-B7qjXrosQaCOwRos .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-B7qjXrosQaCOwRos .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-B7qjXrosQaCOwRos .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-B7qjXrosQaCOwRos .marker{fill:#333333;stroke:#333333;}#mermaid-svg-B7qjXrosQaCOwRos .marker.cross{stroke:#333333;}#mermaid-svg-B7qjXrosQaCOwRos svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-B7qjXrosQaCOwRos .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-B7qjXrosQaCOwRos .cluster-label text{fill:#333;}#mermaid-svg-B7qjXrosQaCOwRos .cluster-label span{color:#333;}#mermaid-svg-B7qjXrosQaCOwRos .label text,#mermaid-svg-B7qjXrosQaCOwRos span{fill:#333;color:#333;}#mermaid-svg-B7qjXrosQaCOwRos .node rect,#mermaid-svg-B7qjXrosQaCOwRos .node circle,#mermaid-svg-B7qjXrosQaCOwRos .node ellipse,#mermaid-svg-B7qjXrosQaCOwRos .node polygon,#mermaid-svg-B7qjXrosQaCOwRos .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-B7qjXrosQaCOwRos .node .label{text-align:center;}#mermaid-svg-B7qjXrosQaCOwRos .node.clickable{cursor:pointer;}#mermaid-svg-B7qjXrosQaCOwRos .arrowheadPath{fill:#333333;}#mermaid-svg-B7qjXrosQaCOwRos .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-B7qjXrosQaCOwRos .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-B7qjXrosQaCOwRos .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-B7qjXrosQaCOwRos .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-B7qjXrosQaCOwRos .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-B7qjXrosQaCOwRos .cluster text{fill:#333;}#mermaid-svg-B7qjXrosQaCOwRos .cluster span{color:#333;}#mermaid-svg-B7qjXrosQaCOwRos div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-B7qjXrosQaCOwRos :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;} 调用 操作 返回 JAVA JDBC库 MySQL 二、创建连接
首先在MySQL官网下载一个jar包然后导入编译器中然后导入。
通过官方文档在MySQL8.0以后连接的地址为com.mysql.cj.jdbc.Driver地址需要添加代码serverTimezoneUTC
这里创建一个DBHelper工具类将这两个参数设为常量便于使用。
class DBHelper {final static String className com.mysql.cj.jdbc.Driver;final static String mysqladdress jdbc:mysql://localhost:3306/java?serverTimezoneUTCuseUnicodetruecharacterEncodingutf8useSSLTRUE;}其中localhost为地址3306为端口java是数据库名称
三、登录MySQL
有了指定的地址还需要用户和密码才能登录数据库。为了安全此处不使用root用户采用新开一个账户来进行对数据库的操作。
create user javalocalhost identified by 123456;
grant all on java to javalocalhost;同样定义常量存储用户名和密码。
private String user, psw;通过构造函数来对user和psw变量赋值。
DBHelper(String user, String psw) {this.user user;this.psw psw;}四、操作数据库
有了地址、用户、密码就可以登录数据库进行操作在此之前需要先实例化Connection类和Statement类并在try-catch语句中创建对数据库的连接和数据传递。
Connection connection;Statement statement;try {Class.forName(className);connection DriverManager.getConnection(mysqladdress, user, psw);statement connection.createStatement();}catch (Exception e) {e.printStackTrace();}连接好数据库之后就可以使用SQL语句对数据库进行操作。
1、返回型操作
定义一个SQL语句在String中实例化一个ResultSet并使用statement传输SQL命令。由于select语句是有返回值的所以使用executeQuery类型进行返回。
String sql select * from teacher;
ResultSet resultSet statement.executeQuery(sql);由于select的返回可能不止一条数据所以使用while循环调用resultSet中的next()函数用于判断是否还有数据返回。然后使用resultSet中的get函数获取字段内容。 while (resultSet.next()) {String tid resultSet.getString(tid);String tname resultSet.getString(tname);String sex resultSet.getString(sex);int age resultSet.getInt(age);String professional_title resultSet.getString(professional_title);int basic_salary resultSet.getInt(basic_salary);System.out.print(tid: tid \t);System.out.print(tname: tname \t);System.out.print(sex: sex \t);System.out.print(age: age \t);System.out.print(professional_title: professional_title \t);System.out.println(basic_salary: basic_salary);}最后操作完毕关闭对象释放内存即可。
resultSet.close();
statement.close();
connection.close();返回型操作完整代码 Connection connection;Statement statement;try {Class.forName(className);connection DriverManager.getConnection(mysqladdress, user, psw);statement connection.createStatement();String sql select * from teacher;ResultSet resultSet statement.executeQuery(sql);while (resultSet.next()) {String tid resultSet.getString(tid);String tname resultSet.getString(tname);String sex resultSet.getString(sex);int age resultSet.getInt(age);String professional_title resultSet.getString(professional_title);int basic_salary resultSet.getInt(basic_salary);System.out.print(tid: tid \t);System.out.print(tname: tname \t);System.out.print(sex: sex \t);System.out.print(age: age \t);System.out.print(professional_title: professional_title \t);System.out.println(basic_salary: basic_salary);}resultSet.close();statement.close();connection.close();} catch (Exception e) {e.printStackTrace();}2、无返回型操作
与有返回操作一致需要先对数据库建立通讯。
Connection connection;Statement statement;try {Class.forName(className);connection DriverManager.getConnection(mysqladdress, user, psw);statement connection.createStatement();} catch (Exception e) {e.printStackTrace();}然后使用sql语句代码这里以insert into语句为例。使用executeUpdate函数进行操作由于没有返回值直接使用statement进行操作后即可。
String sql insert into teacher(tid,tname,sex,age,professional_title,basic_salary) values(19G00001,靳小翠,女,38,教授,16000);
statement.executeUpdate(sql);最后也是需要关闭对象。
无返回型操作完整代码 Connection connection;Statement statement;try {Class.forName(className);connection DriverManager.getConnection(mysqladdress, user, psw);statement connection.createStatement();String sql insert into teacher(tid,tname,sex,age,professional_title,basic_salary) values(19G00001,靳小翠,女,38,教授,16000);statement.executeUpdate(sql);connection.close();statement.close();} catch (Exception e) {e.printStackTrace();}练习题目及完整代码
题目 完整代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;class DBHelper {final static String className com.mysql.cj.jdbc.Driver;final static String mysqladdress jdbc:mysql://localhost:3306/java?serverTimezoneUTCuseUnicodetruecharacterEncodingutf8useSSLTRUE;private String user, psw;DBHelper(String user, String psw) {this.user user;this.psw psw;}public void show_table() {Connection connection;Statement statement;try {Class.forName(className);connection DriverManager.getConnection(mysqladdress, user, psw);statement connection.createStatement();String sql select * from book;ResultSet resultSet statement.executeQuery(sql);while (resultSet.next()) {String bid resultSet.getString(bid);String bname resultSet.getString(bname);String author resultSet.getString(author);double price resultSet.getDouble(price);String press resultSet.getString(press);String type resultSet.getString(type);System.out.print(bid: bid \t);System.out.print(bname: bname \t);System.out.print(author: author \t);System.out.print(price: price \t);System.out.print(press: press \t);System.out.println(type: type);}resultSet.close();statement.close();connection.close();} catch (Exception e) {e.printStackTrace();}}public void change_data() {Connection connection;Statement statement;Statement statement1;try {Class.forName(className);connection DriverManager.getConnection(mysqladdress, user, psw);statement1 connection.createStatement();statement connection.createStatement();String sql select press from book;String sql_professior update book set priceprice*1.1;ResultSet resultSet statement.executeQuery(sql);while (resultSet.next()) {String bookname resultSet.getString(press);if (bookname.equals(清华大学出版社)) {statement1.executeUpdate(sql_professior);}}resultSet.close();statement.close();connection.close();} catch (Exception e) {e.printStackTrace();} finally {show_table();}}public void sort() {Connection connection;Statement statement;try {Class.forName(className);connection DriverManager.getConnection(mysqladdress, user, psw);statement connection.createStatement();String sql select * from book order by price DESC;ResultSet resultSet statement.executeQuery(sql);while (resultSet.next()) {String bid resultSet.getString(bid);String bname resultSet.getString(bname);String author resultSet.getString(author);double price resultSet.getDouble(price);String press resultSet.getString(press);String type resultSet.getString(type);System.out.print(bid: bid \t);System.out.print(bname: bname \t);System.out.print(author: author \t);System.out.print(price: price \t);System.out.print(press: press \t);System.out.println(type: type);}resultSet.close();statement.close();connection.close();} catch (Exception e) {e.printStackTrace();}}public void del() {Connection connection;Statement statement;try {Class.forName(className);connection DriverManager.getConnection(mysqladdress, user, psw);statement connection.createStatement();String sql delete from book where price50 ;statement.executeUpdate(sql);connection.close();statement.close();} catch (Exception e) {e.printStackTrace();}show_table();}public void add() {Connection connection;Statement statement;try {Class.forName(className);connection DriverManager.getConnection(mysqladdress, user, psw);statement connection.createStatement();String sql insert into book(bid,bname,author,price,press,type) values(b00006,数据结构,严蔚敏,48,清华大学出版社,科学类);statement.executeUpdate(sql);connection.close();statement.close();} catch (Exception e) {e.printStackTrace();}show_table();}public void statistics() {Connection connection;Statement statement;try {Class.forName(className);connection DriverManager.getConnection(mysqladdress, user, psw);statement connection.createStatement();String sql select * from book where press(select avg(press) from book);;ResultSet resultSet statement.executeQuery(sql);while (resultSet.next()) {String bid resultSet.getString(bid);String bname resultSet.getString(bname);String author resultSet.getString(author);double price resultSet.getDouble(price);String press resultSet.getString(press);String type resultSet.getString(type);System.out.print(bid: bid \t);System.out.print(bname: bname \t);System.out.print(author: author \t);System.out.print(price: price \t);System.out.print(press: press \t);System.out.println(type: type);}resultSet.close();statement.close();connection.close();} catch (Exception e) {e.printStackTrace();}}
}
public class sql {static final String name java;static final String psw 123456;public static void main(String[] args) {DBHelper dbHelper new DBHelper(name, psw);Scanner scanner new Scanner(System.in);while (true) {System.out.println(1、打印数据\n2、增加工资\n3、排序\n4、删除数据\n5、添加数据\n6、筛选数据);int choose scanner.nextInt();switch (choose) {case 1:dbHelper.show_table();break;case 2:dbHelper.change_data();break;case 3:dbHelper.sort();break;case 4:dbHelper.del();break;case 5:dbHelper.add();break;case 6:dbHelper.statistics();break;}}}
}
