package com.mysql.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
* 1.加载JDBC驱动程序
* 2.建立数据库连接
* 3.创建Statement对象
* 4.执行SQL语句
* 5.处理返回结果
* 6.关闭创建的对象
*/
public class JUtil {
public Connection conn=null;
public Statement stm=null;
public ResultSet result=null;
//连接数据库的方法
public Connection getConn(){
String url="jdbc:mysql://localhost:3306/a27-1";
String user="root";
String password="root";
try {
//1、加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2、创建数据库连接
conn=DriverManager.getConnection(url, user, password);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
//容器:3、创建Statement容器(增删改查的执行方法)
public Statement getStatement(){
try {
if(conn==null){
getConn();
stm=conn.createStatement();
}else{
stm=conn.createStatement();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return stm;
}
//4.执行SQL语句:结果集(查询结果)
public ResultSet query(String sql){
try {
if(stm==null){
result=getStatement().executeQuery(sql);
}else{
result=stm.executeQuery(sql);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return result;
}
//测试查询方法:5、获取并处理结果集
public void queryTest(){
List<Persons> list = new ArrayList<Persons>();
ResultSet rs = query("select * from persons");
try {
while(rs.next()){
int userId = rs.getInt("userId");
String userName = rs.getString("userName");
String address = rs.getString("address");
String city = rs.getString("city");
String card = rs.getString("card");
String types = rs.getString("types");
Persons person = new Persons();
person.setUserId(userId);
person.setUserName(userName);
person.setAddress(address);
person.setCity(city);
person.setCard(card);
person.setTypes(types);
list.add(person);
}
//6、关闭对象
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
for(Persons p:list){
System.out.println("姓名:"+p.getUserName());
}
}
public static void main(String[] args) {
try {
//判断数据库连接成功:false连接 true关闭
boolean isClose = new JUtil().getConn().isClosed();
System.out.println(isClose);
new JUtil().queryTest();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//关闭数据库
public void close(){
try{
if(conn!=null){conn.close();}
if(stmt!=null){stmt.close();}
if(rs!=null){rs.close();}
}catch(SQLException e){
e.printStackTrace();
}
}
//prepareStatement数据库增加
public int insert(Persons per) {
getConn();
int i=0;
try {
prsp = (PreparedStatement)conn.prepareStatement("insert into persons (username,address,city,card,Types) value(?,?,?,?,?)");
prsp.setString(1,per.getUserName());
prsp.setString(2,per.getAddress());
prsp.setString(3,per.getCity());
prsp.setString(4,per.getCard());
prsp.setString(5,per.getTypes());
i=prsp.executeUpdate();
close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return i;
}
//prepareStatement数据库删除
public int delete(String sql){
int i=0;
getConn();
try {
prsp=conn.prepareStatement("delete from Persons where "+sql);
i=prsp.executeUpdate();
close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return i;
}
//prepareStatement数据库修改
public int update(String sql1,String sql2){
int i=0;
getConn();
try {
prsp=conn.prepareStatement("update persons set "+sql1+"where "+sql2);
i=prsp.executeUpdate();
close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return i;
}
//prepareStatement数据库查询
public PreparedStatement select(String sql1){
getConn();
try {
prsp=conn.prepareStatement("select * from Persons where username=?");
prsp.setString(1,sql1);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return prsp;
}
public void selectTest(){
List<Persons> list=new ArrayList<>();
select("123");
try {
rs=prsp.executeQuery();
while(rs.next()){
String username = rs.getString("username");
String address = rs.getString("address");
String city = rs.getString("city");
String card = rs.getString("card");
String Types = rs.getString("Types");
Persons persons = new Persons();
persons.setUserName(username);
persons.setAddress(address);
persons.setCity(city);
persons.setCard(card);
persons.setTypes(Types);
list.add(persons);
}
close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
for(Persons p:list){
System.out.println(p.getUserName());
}
}
}
//主键:自增 LAST_INSERT_ID() mysql数据库自增主键的函数
/*ResultSet rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
int id=1;
while(rs.next()){
id=rs.getInt(1);
}*/
/*ResultSet rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");
int id=1;
while(rs.next()){
id=rs.getInt(1);
}*/