JDBC Java Database Connectivity
JDBC编程步骤:
1,Load the Driver
Class.forName();
2,Connect to the DataBase
Driver Manager.getConnection();
3,Execute the SQL
1,Connection.CreateStatement();
2,Statement.executeQuery();
3,Statement.executeUpdate();
4,Retrieve the result data
循环取得结果 while(rs.next())
5,Show the result data
将数据库中的各种类型转换为java中的类型(getXXX)方法
6,Close()
close the result
close the statement
close the connection
/****************************JDBC实例********************************/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJdbc ... {
public static void main(String[] args) ...{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try ...{
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager
.getConnection("jdbc:mysql://localhost:3306/TestDB?user=root&password=root");
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from Student");
while (rs.next()) ...{
String str = rs.getString("name");
System.out.println(str);
}
} catch (ClassNotFoundException e) ...{
System.out.println("驱动类没有找到");
} catch (SQLException e) ...{
e.printStackTrace();
} finally ...{
try ...{
if (rs != null) ...{
rs.close();
}
if (stmt != null) ...{
stmt.close();
}
if (conn != null) ...{
conn.close();
}
} catch (SQLException e) ...{
e.printStackTrace();
}
}
}
}
DML语句的书写
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager
.getConnection("jdbc:mysql://localhost:3306/TestDB?user=root&password=root");
stmt = conn.createStatement();
String sql = "insert into Student values(3,'stu01') ";
stmt.executeUpdate(sql);
}
批处理语句的书写
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager
.getConnection("jdbc:mysql://localhost:3306/TestDB?user=root&password=root");
String sql = "insert into Student values(?,?)";
ps=conn.prepareStatement(sql);
ps.setInt(1, 5);
ps.setString(2, "stu03");
ps.addBatch();
ps.setInt(1, 6);
ps.setString(2, "stu04");
ps.addBatch();
ps.setInt(1, 7);
ps.setString(2, "stu05");
ps.addBatch();
ps.executeBatch();
}
PreparedStatement语句的书写
try ...{
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager
.getConnection("jdbc:mysql://localhost:3306/TestDB?user=root&password=root");
String sql = "insert into Student values(?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.setString(2, name);
pstmt.executeUpdate();
}
private static String url = "jdbc:mysql://localhost:3306/test";
private static String name = "root";
private static String password = "root";
private JdbcUtils() ...{
}
static ...{
try ...{
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) ...{
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException ...{
return DriverManager.getConnection(url, name, password);
}
public static void release(Connection conn, Statement st, ResultSet rs)
throws SQLException ...{
try ...{
if (rs != null)
rs.close();
} finally ...{
try ...{
if (st != null)
st.close();
} finally ...{
if (conn != null)
conn.close();
}
}
}
}
public class CRUD ... {
/**//*
* @throws SQLException
* @throws ClassNotFoundException
*/
public static void main(String[] args) throws SQLException,
ClassNotFoundException ...{
create();
read();
update();
delete();
}
static void create() throws SQLException ...{
Connection conn = null;
Statement st = null;
try ...{
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String n = "name" + System.currentTimeMillis() % 1000;
String sql = "insert into user(name, age, regist_date) values ('"
+ n + "', 23, now())";
int i = st.executeUpdate(sql);
System.out.println(i);
} finally ...{
JdbcUtils.release(conn, st, null);
}
}
static void read() throws SQLException ...{
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try ...{
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "select id, name, age, regist_date from user";
rs = st.executeQuery(sql);
while (rs.next()) ...{
System.out.print(rs.getInt("id") + " ");
System.out.print(rs.getString("name") + " ");
System.out.print(rs.getInt("age") + " ");
System.out.print(rs.getTimestamp("regist_date") + " ");
System.out.println();
}
} finally ...{
JdbcUtils.release(conn, st, rs);
}
}
static void update() throws SQLException ...{
Connection conn = null;
Statement st = null;
try ...{
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String n = "name" + System.currentTimeMillis() % 1000;
String sql = "update user set name='" + n + "' where id=1";
int i = st.executeUpdate(sql);
System.out.println(i);
} finally ...{
JdbcUtils.release(conn, st, null);
}
}
static void delete() throws SQLException ...{
Connection conn = null;
Statement st = null;
try ...{
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "delete from user where id=4";
int i = st.executeUpdate(sql);
System.out.println(i);
} finally ...{
JdbcUtils.release(conn, st, null);
}
}
}