<>java Connect database

<> step

*
1. Load database
Class.forName("com.mysql.jdbc.Driver");
*
2. Linked database
public static final String url=
"jdbc:mysql://localhost:3306/Product?userUnicode=true&characterEncoding=utf8&useSSL=false"
; public static final String user ="root"; public static final String password=
"root"; Connection conn = DriverManager.getConnection(url, user, password);
*
​ 3. Database manipulation object
String sql="SQL sentence "; PreparedStatement pst=conn.prepareStatement(sql);
// Represents precompiled SQL Statement object . //SQL Statement precompiled and stored in PreparedStatement Object . You can then effectively execute this statement multiple times using this object
*
4. implement SQL sentence
pst.execute();
*
5. close database
conn.close(); pst.close();
<> Basic addition, deletion, modification and query

thinking : First write out the basic framework , Add, delete, modify and check are written in the method

Create commodity class

Commodity category It's about creating attributes ,get set method , Constructing parametric and nonparametric methods ,toString Methods, etc
package com.po; // rewrite equals and HashCode Two methods public class Product { private int id;
private String name; private double price; public Product(int id, String name,
double price) { super(); this.id = id; this.name = name; this.price = price; }
public Product(String name, double price) { super(); this.name = name; this.
price= price; } private String info; private String param; public int getId() {
return id; } public void setId(int id) { this.id = id; } public String getName()
{ return name; } public void setName(String name) { this.name = name; } public
double getPrice() { return price; } public void setPrice(double price) { this.
price= price; } public String getInfo() { return info; } public void setInfo(
String info) { this.info = info; } public String getParam() { return param; }
public void setParam(String param) { this.param = param; } public Product() {
super(); // TODO Auto-generated constructor stub } public Product(int id, String
name, double price, String info, String param) { super(); this.id = id; this.
name= name; this.price = price; this.info = info; this.param = param; } public
Product(String name, double price, String info, String param) { super(); this.
name= name; this.price = price; this.info = info; this.param = param; }
@Override public String toString() { return "Product [id=" + id + ", name=" +
name+ ", price=" + price + ", info=" + info + ", param=" + param + "]"; }
@Override public boolean equals(Object obj) { boolean flag=false; if(this==obj)
{ flag=false; }else if(obj instanceof Product){ Product p=(Product)obj; if(this.
id==p.id&&this.name.equals(p.name)) { flag=true; } } return flag; } @Override
public int hashCode() { // TODO Auto-generated method stub return id+name.
hashCode(); } }
Create interface method class

Define the method class of adding, deleting, modifying and querying ;
public interface IPoroductDAO { // Method of adding int add(Product p); // Method of deletion int delete(
int id); // Method of modification int update(Product p); // according to id Query method Product query(int id);
// All query methods List<Product> query(); }
Because the connection database is redundant, the common parts of the connection database are proposed to the new class

That is, the second connection to the database 1 ,2,5 Step forward
package com.util; import java.sql.Connection; import java.sql.DriverManager;
import java.sql.PreparedStatement; import java.sql.SQLException; public class
DBConnection { public static final String url=
"jdbc:mysql://localhost:3306/Product?useUnicode=true&charaEncoding=utf8"; public
static final String user ="root"; public static final String password="123456";
// Class is executed the first time it is loaded static { try { //1. Load driver Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.
printStackTrace(); }// It is possible to report an error } //2. Database link public static Connection getConn() {
Connection conn =null; try { conn= DriverManager.getConnection(url, user,
password); } catch (SQLException e) { // TODO Auto-generated catch block e.
printStackTrace(); }return conn; } public static void close(Connection conn,
PreparedStatement pst) { try { //5. close database conn.close(); pst.close(); } catch (
SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
Create classes implemented by methods
public class ProductDAOmp1 implements IPoroductDAO { // Put the declaration in the public part for easy access
// Connection to a specific database ( conversation ). implement SQL Statement and returns the result in the context of the connection .
Connection The database of objects can provide tables describing them , Its supported SQL grammar , Its stored procedure , Information about the functions of this connection, etc . This information is in getMetaData Method obtained .
Connection conn =null; PreparedStatement pst =null; }
The methods of adding, deleting, modifying and checking are ProductDAOmp1 In class

This is for more intuitive browsing

Method of adding goods
public int add(Product p) { try { // Get the class containing connection database information conn=DBConnection.getConn();
//3. Database manipulation object String sql="Insert into goods (name,price,info,param)
values(????)" pst=conn.prepareStatement(sql);
//SQL Statement precompiled and stored in PreparedStatement Object . //setString(int parameterIndex, String x)
Sets the specified parameter to the given value Java String value pst.setString(1,p.getName());
// soon p.getName() The obtained value is assigned to the second 1 A place with a question mark pst.setDouble(2.p.getPrice());
// Similarly, will getPrice() The obtained value is assigned to the second 2 A question mark place pst.setString(3, p.getInfo()); pst.setString(4,p.
getParam()); // implement sql sentence pst.executeUpdate(); } catch (SQLException e) { // TODO
Auto-generated catch block e.printStackTrace(); }finally { //5. close database
DBConnection.close(conn, pst); } } }
Method of deleting goods
public int delete(int id) { int flag = 0; try { // Call the written database driver class conn=DBConnection
.getConn(); //3. Database manipulation object String sql="delete from goods where id=?"; pst= conn.
prepareStatement(sql); // Assign values to parameters pst.setInt(1, id);//parameterIndex Number of Representatives ❓
//4. implement SQL flag = pst.executeUpdate(); } catch (SQLException e) { // TODO
Auto-generated catch block e.printStackTrace(); }finally { //5. close database
DBConnection.close(conn, pst); } return flag; }
according to id Method of query
public Product query(int id) { // Unlike before, you need to use sets Will from ResultSet The obtained value is assigned to Arraylist For output
// Connect database conn=DBConnection.getConn(); // Create collection List<Product> list=new ArrayList<
Product>(); //3. Database manipulation object String sql="select * from goods where id=?"; pst=conn.
prepareStatement(sql); pst.set(1,p.getId()); // because executeQery There will be ResultSet Return value of
ResultSet rs=pst.executeQuery(); // take rs The value in is taken out to the set while(rs.next()){ Product p=
new Product(); p.setId(rs.getInt("id"));// take rs Obtained from id Assign to p p.setName(rs.getString(
"name")); p.setPrice(rs.getDouble("price")); p.setInfo(rs.getString("info")); p.
setParam(rs.getString("param")); // Add to container list.add(p); } // Traversal output for(int i=0;i<
list.size();i++) { System.out.println(list.get(i)); } } catch (SQLException e) {
// TODO Auto-generated catch block e.printStackTrace(); }finally { // close
DBConnection.close(conn, pst); } return null; }
Query out all elements
public List<Product> query() { // Connect database ArrayList list=new ArrayList<Product>();
conn=DBConnection.getconn(); // Database manipulation object String sql="select * from goods" pst=
conn.prepareStatement(sql); ResultSet rs= pst.executeQuery(); while(rs.next()){
Product p new Product(); p.setInt(rs.getInt("id")); p.setString(rs.getString(
"name")); p.setDouble (rs.getDouble("price")); p.setInfo(rs.getString("info"));
p.setParam(rs.getString("param")); list.add(p); } for(int i=0;i<;list.size();i++
){ System.out.println(list.get(i)); } } catch (SQLException e) { // TODO
Auto-generated catch block e.printStackTrace(); }finally { //5. close database
DBConnection.close(conn, pst); } } catch (SQLException e) { // TODO
Auto-generated catch block e.printStackTrace(); }finally { //5. close database
DBConnection.close(conn, pst); } }
according to id Modify the data
public int update(Product p) { // Connect database conn=DBConnection.getconn(); // Database manipulation object
String sql="update goods set name=?price=? where id =?" pst=conn.
prepareStatement(sql); pst.setString(1,p.setName()); pst.setDouble(2,p.setPrice(
)); pst.setInt(3,p.getId()); pst.executeUpdate(); } } catch (SQLException e) {
// TODO Auto-generated catch block e.printStackTrace(); } return 0; } }
In this way, several methods are constructed

to write test Class
package com.view; import java.util.List; import java.util.Scanner; import
com.dao.IPoroductDAO; import com.dao.ProductDAOmp1; import com.po.Product;
public class Test { public static void main(String[] args) { // Cycle to write a menu switch //
input 1-- add to 2-- delete 3-- query Scanner sc = new Scanner(System.in); IPoroductDAO pro = new
ProductDAOmp1(); while (true) { System.out.println(" Please enter your action :1-- add to 2-- delete 3-- Query single
4-- Query all 5-- Make product modification 6- Exit the system "); int k = sc.nextInt(); if (k == 1) {
System.out.println(" input name:"); String name = sc.next();
System.out.println(" input price:"); double price = sc.nextDouble();
System.out.println(" input info:"); String info = sc.next();
System.out.println(" input param:"); String param = sc.next(); Product p = new
Product( name, price, info, param); pro.add(p); } else if (k == 2) {
System.out.println(" Enter the to delete id:"); int id = sc.nextInt(); // call delete method int flag
= pro.delete(id); if (flag == 1) { System.out.println(" Successfully deleted id by "+id+" Commodity "); }
else if(flag == 0){ System.out.println(" This item was not found "); } } else if (k == 3) {
System.out.println(" Enter the to query id:"); int id = sc.nextInt(); pro.query(id); }else
if(k==5){ System.out.println(" Please enter the item you want to modify id number "); int a=sc.nextInt();
System.out.println(" Please enter a name to modify "); String b=sc.next();
System.out.println(" Please enter the price to modify "); Double c=sc.nextDouble(); Product p = new
Product(a,b,c); pro.update(p); }else if(k==6) { System.exit(0);; } } } }
*

Technology
©2019-2020 Toolsou All rights reserved,
C++ of string of compare usage MySQL Basics Commonly used sentence ( Add / delete / modify query )C Language of a Gobang game implementation QT5.9 Use of learning notes QSqlQuery Method of Qt Getting Started tutorial 【 Basic controls 】QCalendarWidget calendar control java Polymorphic array of web Front end signature plug-in _signature_pad Plug in implements electronic signature function centos7 install RabbitMqspringboot use redis Experiment 4 Automated test tools - software test