访问数据库的代码示例
数据库中建的表:
CREATE TABLE `person` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(20) default NULL,
`age` int(4) default NULL,
PRIMARY KEY (`id`)
)
以保存和查询为例,保存与更新查不多
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class UserService {
public Connection getConnection() throws ClassNotFoundException,
SQLException {
// 加载MySQL的JDBC的驱动
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/test";
String username = "root";
String password = "123456";
// 创建与MySQL数据库的连接类的实例
Connection conn = DriverManager.getConnection(url, username, password);
System.out.println("Database connection established");
return conn;
}
public void save() throws Exception {
Connection conn = getConnection();
try {
// 禁用自动提交
conn.setAutoCommit(false);
System.out.println("默认的事务隔离级别:" + conn.getTransactionIsolation());
// 用conn创建Statement对象类实例,容易发生sql注入问题
Statement sql_statement = conn.createStatement();
/*
用PrepareStatement执行
String sql = "insert into person values(?,?,?)"
PrepareStatement ps = conn.prepareStatement(sql);
//给占位符赋值
ps.setInt(1,10);
ps.setString(2,"名字");
ps.setInt(3,18);
ps.executeUpdate();//此处不能传入sql,传入sql后sql语句中就不能用占位符,PrepareStatement是继承Statement
//如果传入sql则执行的是Statement的方法.
*/
// 插入数据
sql_statement.executeUpdate("insert into person values(10,'aabb', 88)");
System.out.println("Insert success");
//提交事务
conn.commit();
} catch (Exception e) {
e.printStackTrace();
if (conn != null){
//事务回滚
conn.rollback();
}
} finally {
if (conn != null) {
// 关闭连接
try {
conn.close();
System.out.println("Database connection terminated");
} catch (Exception e) { /* ignore close errors */
}
}
}
}
public void query() throws Exception {
Connection conn = getConnection();
try {
// 用conn创建Statement对象类实例
Statement sql_statement = conn.createStatement();
// 执行查询,用ResultSet类的对象,返回查询的结果
String query = "select * from person";
ResultSet result = sql_statement.executeQuery(query);
// 处理结果
while (result.next()) {
int id = result.getInt("id");
String name = result.getString("name");
String age = result.getString("age");
/*
此处可以有一个对象返回
User user = new User();
user.setName(name);
user.setAge(age);
如果返回一个list
list.add(user);
*/
System.out.println("id: " + id + " name: " + name + " age: " + age);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (conn != null) {
// 关闭连接
try {
conn.close();
System.out.println("Database connection terminated");
} catch (Exception e) { /* ignore close errors */
}
}
}
}
}
原生JDBC明白了,其它封装的底层离不开这些核心内容!