MyEclipse in JDBC Connection MySQL and simple additions and deletions to change

Not much nonsense to say, directly on the operation of the diagram, the operation will be more thorough understanding.

1, MyEclipse in the project structure, the first web-inf-lib under the import jar package, note that the Web APP libraries This directory, the following will be useful to the place

2. Connection statement

Package utils;

Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;

Import Com.sun.corba.se.spi.orbutil.fsm.Guard.Result;

public class Basedao {
First step: Set four constants (URL database address, username database user name, password database password, driver database driver)
private static final String URL = "Jdbc:mysql://127.0.0.1:3306/petshop?characterencoding=utf-8";
private static final String USERNAME = "root";
private static final String PASSWORD = "OK";
private static final String DRIVER = "Com.mysql.jdbc.Driver";//web App Libraries--file name under mysql-connector.jar--

1. Load driver, with static block
JDBC is the interface (rule) that Java provides to the major database developers, and the driver is the concrete implementation of these rules
static{
try {
Class.forName (DRIVER);//requires the JVM to find and load the specified class, which is the driver class that registers the MySQL implementation
} catch (ClassNotFoundException e) {
E.printstacktrace ();
}
}

2. Get the database connection by setting the parameters in the DriverManager drive management class
Public Connection getconnection () throws sqlexception{
Return drivermanager.getconnection (Url,username,password);
}

3. Create a precompiled execution object PreparedStatement
Public PreparedStatement createpreparedstatement (Connection conn,
String Sql,object. Params) throws sqlexception{
Gets the execution object from the current Conn object, putting the precompiled SQL statement in it
PreparedStatement pstmt = conn.preparestatement (sql);
If there is a placeholder in the precompiled SQL, it means that you want to assign a value
if (params!=null && params.length>0) {
The element one by one in the params object collection is addressed to the placeholder by traversal
for (int i=1;i<=params.length;i++) {
Pstmt.setobject (i, params[i-1]);
}
}
Return pstmt;//precompiled execution object with SQL statement
}

4. Create a public DML execution method
public int executedml (String sql,object ... params) {
int result = 0;//is used to return the number of rows affected to determine whether the execution succeeded
Create two database objects
Connection conn = null;//Database Connection object
PreparedStatement pstmt = null;//Precompiled Execution object, receiving

try {
conn = This.getconnection ();
pstmt = this.createpreparedstatement (conn, SQL, params);
result = Pstmt.executeupdate ();
} catch (SQLException e) {
E.printstacktrace ();
} finally{
CloseAll (Conn,pstmt,null);
}
return result;
}
5. Releasing Public resource methods
void CloseAll (Connection conn, PreparedStatement pstmt,
ResultSet rs) {

try {
if (rs!=null)
Rs.close ();
} catch (SQLException e) {
E.printstacktrace ();
}finally{
try {
if (pstmt!=null)
Pstmt.close ();
} catch (SQLException e) {
E.printstacktrace ();
}finally{
try {
if (conn!=null)
Conn.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}
}

}

}

3, test the code in the class, to achieve simple additions and deletions to search

Package utils;

Import java.sql.Connection;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
Import java.sql.SQLException;
Import Java.util.Scanner;

public class Testdml {
static Scanner input = new Scanner (system.in);
public static void Main (string[] args) {
SYSTEM.OUT.PRINTLN ("Please select the action \n1 you want to perform." Execution 2. Delete 3. Modify 4. Check All ");
TESTDML test = new Testdml ();
int choose = Input.nextint ();
Switch (choose) {
Case 1://New
Test.insertnewgrade ();
Break
Case 2://Delete
Test.delgrade ();
Break
Case 3://Modification
Test.updategrade ();
Break
Case 4://Query
Test.selectall ();
Break
}
}

private void SelectAll () {
Basedao bd = new Basedao ();
Connection conn = null;
String sql = "SELECT * from Petstore";
PreparedStatement Pstmt=null;
ResultSet rs = null;
try {
conn = Bd.getconnection ();//Get Database link object
pstmt = bd.createpreparedstatement (conn, SQL);
rs = Pstmt.executequery ();
System.out.println ("Id\tname\tpassword\tbalance");
while (Rs.next ()) {
System.out.println (Rs.getint (1) + "\ T" +rs.getstring (2) + "\ T" +rs.getstring (3) + "\ T" +rs.getint (4));
}
} catch (SQLException e) {
E.printstacktrace ();
}finally{
Bd.closeall (CONN,PSTMT,RS);
}
}

private void Updategrade () {
System.out.println ("Please enter the ID number to be modified:");
int Id = Input.nextint ();
System.out.println ("Please enter a new ID:");
int id = input.nextint ();
System.out.println ("Please enter a new name:");
String name = Input.next ();
System.out.println ("Please enter a new password:");
String password = input.next ();
System.out.println ("Please enter a New Balance:");
String balance = Input.next ();
To execute an INSERT statement, you need to pass in SQL and params
Basedao bd = new Basedao ();
String sql = "Update petstore set id=?", Name=?,password=?,balance =? where id=? ";

Object[] params = {Id,name,password,balance,id};
int result = BD.EXECUTEDML (sql, params);

if (result>0) {
SYSTEM.OUT.PRINTLN ("modified successfully");
}else{
SYSTEM.OUT.PRINTLN ("modification failed");
}
}

private void Delgrade () {
System.out.println ("Please enter the ID to delete:");
int id = input.nextint ();
To execute an INSERT statement, you need to pass in SQL and params
Basedao bd = new Basedao ();
String sql = "Delete from Petstore where id=?";
Object[] params = {ID};
int result = BD.EXECUTEDML (sql, params);

if (result>0) {
System.out.println ("delete succeeded");
}else{
System.out.println ("delete failed");
}
}

private void Insertnewgrade () {
Execute new (grade number, grade name)
System.out.println ("Please enter ID:");
int id = input.nextint ();
System.out.println ("Please enter Name:");
String name = Input.next ();
System.out.println ("Please enter password:");
String password = input.next ();
System.out.println ("Please enter balance:");
String balance = Input.next ();
To execute an INSERT statement, you need to pass in SQL and params
Basedao bd = new Basedao ();
String sql = "INSERT into Petstore value (?,?,?,?)";

Object[] params = {id,name,password,balance};
int result = BD.EXECUTEDML (sql, params);

if (result>0) {
System.out.println ("new success");
}else{
SYSTEM.OUT.PRINTLN ("New Failure");
}
}

}

4, the effect, the following is a table has a data

1. Insert

2. Delete

3. Update

4. View Table Data

Above!!!!!

MyEclipse in JDBC Connection MySQL and simple additions and deletions to change