MYSQL的查增删改

Test.java

package com.geek99.demo;

import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
public class Test {
public static void main(String[] args) throws SQLException {
Connection conn=(Connection) DBUtil.open();
String sql="select id,name,email from CustomerTb1";


// try {
// Class.forName("com.mysql.jdbc.Driver");
// } catch (ClassNotFoundException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
//
// try {
// conn=(Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/Geek99DB","root","000000");
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
//
try {
Statement stmt=(Statement) conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);
while(rs.next()){
int id=rs.getInt(1);
String name=rs.getString(2);
String email=rs.getString(3);
System.out.println(id+","+name+","+email);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
// if(conn!=null){
// conn.close();
// }
DBUtil.close(conn);
}
}

}

DBUtil.java

package com.geek99.demo;
import java.io.FileReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtil {
private static String driver;
private static String url;
private static String username;
private static String password;
static{
Properties prop=new Properties();
Reader in;
try {
in=new FileReader("src\\config.properties");
prop.load(in);
} catch (Exception e) {
e.printStackTrace();
}
driver=prop.getProperty("driver");
url=prop.getProperty("url");
username=prop.getProperty("username");
password=prop.getProperty("password");
}
public static Connection open(){
try {
Class.forName(driver);
return DriverManager.getConnection(url,username,password);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void close(Connection conn){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}

//配置文件：用于数据库的账号密码修改

config.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/Geek99DB
username=root
password=000000

//查增删改的main()类

Test2：

package com.geek99.demo;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class Test2 {
public static void main(String[] args) {
//createTable();
//insertTable();
//updateTable();
//deleteTable();
//queryTable();
queryTable2();
}


static List queryTable2(){
Connection conn=DBUtil.open();
String sql="select id,name from UserTb1";
try {
Statement stmt=(Statement) conn.createStatement();
ResultSet rs=stmt.executeQuery(sql);
List list=new ArrayList();
while(rs.next()){
int id=rs.getInt(1);
String name=rs.getString(2);
User u=new User();
u.setId(id);
u.setName(name);
list.add(u);
//System.out.println(id+","+name);
}
System.out.println(list);
return list;
//stmt.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
DBUtil.close(conn);
}
return null;
}
}

//创建一个User.java类，用于存储封装查询得到的数据

User.java

package com.geek99.demo;


public class User {
private int id;
private String name;
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 String toString(){
return id+":"+name;
}
}