-
1 参考代码
-
2 视频
package com.ahdy.jdbc;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;
import java.util.Scanner;
import com.ahdy.javabean.*;
public class DBUtil {
//移植到其他电脑时注意，需要修改端口号；连接其他数据库时，需要修改数据库的名字：student
static PreparedStatement pstmt=null;
static String sql="";
static Connection dbConn=null;
static ResultSet rs=null;
static Properties pp=null;
static InputStream fis=null;
private static String url;
private static String driver;
private static String username;
private static String password;
static {
try {
pp=new Properties();
fis=DBUtil.class.getClassLoader().getResourceAsStream("dbinfo.properties");
pp.load(fis);
url=pp.getProperty("url");
driver=pp.getProperty("driver");
username=pp.getProperty("username");
password=pp.getProperty("password");
Class.forName(driver);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void closeMysql() {
try {
if(rs!=null)
rs.close();
if(pstmt!=null)
pstmt.close(); //操作对象关闭
if(dbConn!=null)
dbConn.close();//连接对象关闭
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void connectMysql() {
try {
Class.forName(driver);
//加载JDBC驱动
// 连接服务器和数据库
dbConn=DriverManager.getConnection(url,username,password);
} catch (Exception e) {
e.printStackTrace();
}
}
//执行增\删\改的操作,sql是sql命令，params是参数数组
public static int executeUpdate(String sql,String[] params) {
int length=0;
try {
connectMysql();
pstmt=dbConn.prepareStatement(sql);
if(params!=null) {
for(int i=0;i<params.length;i++)
pstmt.setString(i+1, params[i]);
}
length=pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
closeMysql();
}
return length;
}
//执行查询操作,后面将进行二次封装，因为rs在返回前不能关闭
public static ResultSet executeQuery(String sql,String[] params) {
try {
connectMysql();
pstmt=dbConn.prepareStatement(sql);
if(params!=null) {
for(int i=0;i<params.length;i++)
pstmt.setString(i+1, params[i]);
}
rs=pstmt.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
public static ArrayList<Book> executeQueryPageBook(String sql,int[] params) {
ArrayList<Book> all=new ArrayList<Book>();
try {
connectMysql();
pstmt=dbConn.prepareStatement(sql);
if(params!=null) {
for(int i=0;i<params.length;i++)
pstmt.setInt(i+1, params[i]);
}
rs=pstmt.executeQuery();
while(rs.next()) {
Book book=new Book();
book.setId(rs.getInt("id")+"");
book.setName(rs.getString("name"));
book.setAuthor(rs.getString("author"));
book.setBookCount(rs.getInt("bookcount"));
book.setPrice(rs.getDouble("price"));
book.setPublish(rs.getString("publish"));
book.setImg(rs.getString("img"));
all.add(book);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
closeMysql();
}
return all;
}
public static ArrayList<User> executeQueryPageUser(String sql,int[] params) {
ArrayList<User> all=new ArrayList<User>();
try {
connectMysql();
pstmt=dbConn.prepareStatement(sql);
if(params!=null) {
for(int i=0;i<params.length;i++)
pstmt.setInt(i+1, params[i]);
}
rs=pstmt.executeQuery();
while(rs.next()) {
User user=new User();
user.setId(Integer.parseInt(rs.getString("id"))); //注意：双引号中的名称要和数据库中表的字段名称保持一致
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setGrade(Integer.parseInt(rs.getString("grade")));
user.setEmail(rs.getString("email"));
all.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
closeMysql();
}
return all;
}
public static ArrayList<Book> executeQueryBook(String sql,String[] params) {
ArrayList<Book> all=new ArrayList<Book>();
try {
connectMysql();
pstmt=dbConn.prepareStatement(sql);
if(params!=null) {
for(int i=0;i<params.length;i++)
pstmt.setString(i+1, params[i]);
}
rs=pstmt.executeQuery();
while(rs.next()) {
Book book=new Book();
book.setId(rs.getInt("id")+"");
book.setName(rs.getString("name"));
book.setAuthor(rs.getString("author"));
book.setBookCount(rs.getInt("bookcount"));
book.setPrice(rs.getDouble("price"));
book.setPublish(rs.getString("publish"));
book.setImg(rs.getString("img"));
all.add(book);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
closeMysql();
}
return all;
}
public static ArrayList<User> executeQuery2(String sql,String[] params) {
ArrayList<User> all=new ArrayList<User>();
try {
connectMysql();
pstmt=dbConn.prepareStatement(sql);
if(params!=null) {
for(int i=0;i<params.length;i++)
pstmt.setString(i+1, params[i]);
}
rs=pstmt.executeQuery();
while(rs.next()) {
User user=new User();
user.setId(Integer.parseInt(rs.getString("id"))); //注意：双引号中的名称要和数据库中表的字段名称保持一致
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setGrade(Integer.parseInt(rs.getString("grade")));
user.setEmail(rs.getString("email"));
all.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
closeMysql();
}
return all;
}
/* //新增 ctrl+shift+/ ctrl+/
public static void insertData() throws SQLException {
String uname="zhm13";
String pwd="123456";
String grade="3";
String email="jdxwl@126.com";
sql="INSERT INTO `tuserlogin`(`username`,`password`,`grade`,`email`) VALUES (?,?,?,?);";
pstmt=dbConn.prepareStatement(sql);
pstmt.setString(1, uname);
pstmt.executeUpdate();
System.out.println("新增完成");
}
//修改
public static void updateData() throws SQLException {
sql="update `tuserlogin` set password='111111' where id='12'";
stmt.executeUpdate(sql);
System.out.println("修改完成");
}
//删除
public static void deleteData(String id) throws SQLException {
sql="delete from tuserlogin where id="+id;
stmt.executeUpdate(sql);
System.out.println("删除完成");
}
//全部查询
public static void selectAll() throws SQLException {
sql="select * from tuserlogin";
rs= stmt.executeQuery(sql); //查询结果赋值给rs
while (rs.next()) {
System.out.println(rs.getInt("id")+"\t"+rs.getString("username")+"\t"+ rs.getString("password"));
//通过数据表tuserlogin中的字段名称username\password获取信息
}
}
//id查询
public static boolean selectById(String id) throws SQLException {
sql="select * from tuserlogin where id="+id;
rs= stmt.executeQuery(sql); //查询结果赋值给rs
if(rs.next()) {
return true;
}else return false;
}
//身份认证
public static boolean identity(String username,String password) {
boolean flag=false;
sql="select * from tuserlogin where username='"+username+"' and password='"+password+"'";
//select * from tuserlogin where username='aaa' and password='sss'
try {
rs= stmt.executeQuery(sql);
if(rs.next())flag=true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flag;
}*/
}
