使用軟件：mysql、eclipse
鏈接步驟：
1.注冊驅動
2.創建一個連接對象
3.寫sql語句
4.執行sql語句并返回一個結果或者結果集
5.關閉鏈接(一般就是connection、statement、setresult)這三個連接對象，關閉順序一般是(setresult? ? --->? statement? -->? setresult? )
一、直接連接方法：(這種方法就是講sql語句和結果所有的步驟寫在一起) 不建議使用該方法
1 public static voidmain(String[] args) {2 String url = "jdbc:mysql://localhost:3306/students";3 String user = "root";4 String password = "admin";5 Connection conn = null;6 Statement st = null;7
8 try{9 //1. 注冊驅動
10 Class.forName("com.mysql.jdbc.Driver");11 //2. 創建一個鏈接對象
12 conn =DriverManager.getConnection(url,user,password);13 //3. 創建一個sql語句的發送命令對象
14 String sql = "insert into student values('2001','Tom','20','7000')";15 st=conn.createStatement();16 //4. 執行sql語句，拿到查詢的結果集對象
17 st.executeQuery(sql);20 } catch(Exception e) {21 e.printStackTrace();22 }finally{23 //5. 關閉鏈接 ，命令對象 ，結果集
24 if(st != null) {25 try{26 st.close();27 } catch(Exception e) {28 e.printStackTrace();29 }30 }31 if(conn != null) {32 try{33 conn.close();34 } catch(Exception e) {35 e.printStackTrace();36 }37 }38 }
二、建立工具類方法，將必要的幾步寫一個類，使用的時候直接調用(建議使用)
1.注冊驅動、創建連接對象、關閉資源? ? 這三部一般可以寫一個類，由于寫sql語句和執行sql語句的結果不一致，所以可以將其在用到的時候在寫
2.一般寫工具類都是寫成靜態方法，以方便調用
//這是工具類：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcUtils {
private static String driverName = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/student_achievement_system";
private static String user = "root";
private static String password = "admin";
/**
* 鏈接數據庫
*/
static {
try {
Class.forName(JdbcUtils.driverName);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 獲取鏈接對象connection
* @return
*/
public static Connection getConnection() {
try {
return DriverManager.getConnection(JdbcUtils.url, JdbcUtils.user, JdbcUtils.password);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 關閉資源
* @param conn
* @param st
* @param rs
*/
public static void close(Connection conn,Statement st,ResultSet rs) {
if(rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if(st != null) {
try {
st.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
//這是對數據庫的基本操作類
// 增加、刪除、更新、查找一條、查找所有的方法
public class StudentsDaoImpl implements IStudentsDao {
//增加
@Override
public int save(Students student) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
String sql = "insert into students values(?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setInt(1, student.getStudentId());
ps.setString(2, student.getStudentName());
ps.setString(3, student.getSex());
ps.setString(4, student.getPhoneNo());
ps.setString(5, student.getAddress());
ps.setDate(6, (Date) student.getBirthday());
int row = ps.executeUpdate();
return row;
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.close(conn, ps, null);
}
return 0;
}
//刪除
@Override
public int delete(int studentId) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
String sql = "delete from students where studentId=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, studentId);
int row = ps.executeUpdate();
return row;
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.close(conn, ps, null);
}
return 0;
}
//更新
@Override
public int update(int studentId, Students student) {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
String sql = "update students set studentName=?,sex=?,phoneNo=?,address=?,birthday=? where studentId=?";
ps = conn.prepareStatement(sql);
ps.setString(1, student.getStudentName());
ps.setString(2, student.getSex());
ps.setString(3, student.getPhoneNo());
ps.setString(4, student.getAddress());
ps.setDate(5, ((Date) student.getBirthday()));
ps.setInt(6, studentId);
int row = ps.executeUpdate();
return row;
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.close(conn, ps, null);
}
return 0;
}
//查找一條數據
@Override
public Students getByStudentId(int studentId) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select * from students where studentId=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, studentId);
rs = ps.executeQuery();
if(rs.next()) {
Students student = new Students();
student.setStudentId(rs.getInt("studentId"));
student.setStudentName(rs.getString("studentName"));
student.setSex(rs.getString("sex"));
student.setPhoneNo(rs.getString("phoneNo"));
student.setAddress(rs.getString("address"));
student.setBirthday(rs.getDate("birthday"));
return student;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.close(conn, ps, rs);
}
return null;
}
//查找所有數據
@Override
public List getAll() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select * from students";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
List studentsList = new ArrayList<>();
while(rs.next()) {
Students student = new Students();
student.setStudentId(rs.getInt("studentId"));
student.setStudentName(rs.getString("studentName"));
student.setSex(rs.getString("sex"));
student.setPhoneNo(rs.getString("phoneNo"));
student.setAddress(rs.getString("address"));
student.setBirthday(rs.getDate("birthday"));
studentsList.add(student);
}
return studentsList;
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.close(conn, ps, rs);
}
return null;
}
}
內容來源于網絡如有侵權請私信刪除
