catalogue
4. Simple MySQL addition, deletion, modification and query operations
preface
Hello, I am. Personal homepage: Blog of
The idea demo used in this article mainly talks about how to use idea to complete jdbc connection and Java to realize simple addition, deletion, modification and query operations of MySQL.
Friends passing by point a compliment and pay attention before you leave. Welcome to the comment area for communication. It's not too late to start trying. It's better to start with this article!
Everyone grow up together! Refill
**1. * * new project
Create a new project, as shown in the following figure:
Select Java à for the next step, as shown in the following figure: (note that if jdk is recommended to use jdk1.8 version, if not, it can be replaced in the project SDK, Add JDK, find the place where jdk1.8 is placed on your computer, and if not, download it yourself.)
Continue to the next step
Create a project name (just start by yourself, and note that the project name should not be capitalized), find a storage address, and decide by yourself.
**2. * * add jar package
The general default location is as follows: C:Program Files (x86)MySQLConnector J 8.0
File à project Structure à modules à dependencies à plus sign add jar package
Find the location of C:Program Files (x86)MySQLConnector J 8.0 under drive C
Select OK. complete
Adding jar package succeeded
3.jdbc * * * * connection
Create a new Java file in the package as follows
After implementing JDBC connection, my code and result screenshots are as follows:
package?com.wang.dao;
import?java.sql.*;
//Adding, deleting, modifying and querying MySQL with Java
public?class?Test1Demo?{
????public?static?void?main(String[]args){
????????String?url="jdbc:mysql://localhost:3306/ishop? Useunicode=true&characterencoding=utf-8&usessl=false&servertimezone=gmt%2b8 "; //mysql8 connection string, with a time zone of 5 more than the previous one
????????String?name="root";
????????String?password="root";
????????String?sql="SELECT?*from?tbl_commoditytype";
????????//1. Load drive
????????try?{
????????????Class.forName("com.mysql.cj.jdbc.Driver");//When there is an error prompt, the cursor moves to the error, alt+enter,
????????????try?{
????????????????//2. Create a connection
????????????????Connection??connection=?DriverManager.getConnection(url,name,password);
????????????????//3. Create a command window
????????????????Statement?statement?=?connection.createStatement();
????????????????//4. Execute the statements in the command window
????????????????ResultSet?resultSet?=?statement.executeQuery(sql);
????????????????//5. Process the returned result set
????????????????while?(resultSet.next()){
????????????????????//Print each column of a row
??????????????????System.out.println(resultSet.getInt(1)+" "+resultSet.getString(2));
????????????????}
????????????????//6. Close resources
????????????????resultSet.close();
????????????????statement.close();
????????????????connection.close();
????????????}?catch?(SQLException?e)?{
????????????????e.printStackTrace();
????????????}
????????}?catch?(ClassNotFoundException?e)?{
????????????e.printStackTrace();
????????}
????}
}
Pay attention to whether the following location databases and SQL statements exist in their own MySQL, and whether they match. And whether the user name and password are your own.
4. Simple MySQL addition, deletion, modification and query operations
The operations of adding, deleting and modifying MySQL are as follows: (look carefully at the things commented out. These three operations are changed. Some of the code in the comments has been run three times.)
package com.wang.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Test01 {
??? public static void main(String[] args) {//psvm enter can be typed directly
??????? //1. Load drive
??????? try {
??????????? Class.forName("com.mysql.cj.jdbc.Driver");
??????? } catch (ClassNotFoundException e) {
??????????? e.printStackTrace();
??????? }
??????? //2. Get the link and drive the manager
??????? String url="jdbc:mysql://localhost:3306/ishop?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8";
??????? String user="root";
??????? String password="root";
??????? Connection connection = null;
??????? try {
??????????? connection = DriverManager.getConnection(url, user, password);
??????? } catch (SQLException e) {
??????????? e.printStackTrace();
??????? }
??????? //3. Get command
??????? Statement statement = null;
??????? try {
??????????? statement = connection.createStatement();
??????? } catch (SQLException e) {
??????????? e.printStackTrace();
??????? }
?????? // String sql="insert into tbl_commoditytype (id,name) values (6,'AA')"; // In this place, I later found that I forgot to set the self growth ID in my database table, so I directly wrote the value of ID here. After that, add, delete and modify the operation in turn to view the three result graphs of the table
??????? //String sql="update tbl_commoditytype set name ='bb' where id=6";
??????? String sql="delete from tbl_commoditytype where id=6";
??????? int i = 0;
??????? //executeUpdate is for addition, deletion and modification
???? ???//4. Get the result set and process it
??????? try {
??????????? i = statement.executeUpdate(sql);
??????? } catch (SQLException e) {
??????????? e.printStackTrace();
??????? }
??????? System.out.println(i);//South enter can quickly create System.out.println()
??????
??????? //5. Close resources
??????? //The used connection, statement, two interfaces, resultSet and an implementation class (result set)
??????? try {
??????????? statement.close();
??????? } catch (SQLException e) {
??????????? e.printStackTrace();
??????? }
??????? try {
??????????? connection.close();
??????? } catch (SQLException e) {
??????????? e.printStackTrace();
??????? }
??? }
}
Realize simple query operation
First copy and paste the code of the addition, deletion and modification operation (how can programmers not copy and paste hey), and then change the addition, deletion and modification into a query statement String sql= "select*from tbl_commoditytype”; Corresponding executeUpdate(); Change to executQuery(). The details are as follows
package com.wang.demo;
import java.sql.*;
/**
?* Slash double star carriage return can get this annotation
?* Query with Statement
?*/
public class Test02 {
??? public static void main(String[] args) {
??????? //1. Load drive
??????? try {
??????????? Class.forName("com.mysql.cj.jdbc.Driver");
??????? } catch (ClassNotFoundException e) {
??????????? e.printStackTrace();
??????? }
??????? //2. Get the link and drive the manager
??????? String url="jdbc:mysql://localhost:3306/ishop?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8";
??????? String user="root";
??????? String password="root";
??????? Connection connection = null;
??????? try {
??????????? connection = DriverManager.getConnection(url, user, password);
??????? } catch (SQLException e) {
??????????? e.printStackTrace();
??????? }
??????? //3. Get command
??????? Statement statement = null;
????? ??try {
??????????? statement = connection.createStatement();
??????? } catch (SQLException e) {
??????????? e.printStackTrace();
??????? }
??????? // String sql="insert into tbl_commoditytype (id,name) values (6,'AA')";
??????? //String sql="update tbl_commoditytype set name ='bb' where id=6";
??????? //String sql="delete from tbl_commoditytype where id=6";
??????? String sql="select *from tbl_commoditytype";
??????? ResultSet resultSet=null;
??????? //executeUpdate is for addition, deletion and modification
??????? // 4. Get the result set and process it
??????? try {
??????????? resultSet = statement.executeQuery(sql);
??????????? //There are two methods to process the result set, if (one record) /while (uncertain or multiple data)
??????????? while(resultSet.next()){
?????????????? String o= resultSet.getInt(1)+" "+resultSet.getString(2);
??????????????? //Because the first column of my table is int and the second column is string. You can also replace 2 with name, that is, replace the index (columnindex) with column name (columnlabel}
??????????????? System.out.println(o);
??????????? }
??????? } catch (SQLException e) {
??????????? e.printStackTrace();
??????? }
??????? //South enter can quickly create System.out.println()
??????? //5. Close resources
??????? //The used connection, statement, two interfaces, resultSet and an implementation class (result set)
??????? try {
??????????? statement.close();
??????? } catch (SQLException e) {
??????????? e.printStackTrace();
??????? }
??????? try {
??????????? connection.close();
??????? } catch (SQLException e) {
??????????? e.printStackTrace();
??????? }
??? }
}
First of all, I would like to introduce myself. I graduated from Jiaotong University in 13 years. I once worked in a small company, went to large factories such as Huawei OPPO, and joined Alibaba in 18 years, until now. I know that most junior and intermediate Java engineers who want to improve their skills often need to explore and grow by themselves or sign up for classes, but there is a lot of pressure on training institutions to pay nearly 10000 yuan in tuition fees. The self-study efficiency of their own fragmentation is very low and long, and it is easy to encounter the ceiling technology to stop. Therefore, I collected a "full set of learning materials for java development" and gave it to you. The original intention is also very simple. I hope to help friends who want to learn by themselves and don't know where to start, and reduce everyone's burden at the same time. Add the business card below to get a full set of learning materials