JDBC简单的示例

JDBC 编程步骤

1. 加载驱动程序：

Class.forName(driverClass)
//加载MySql驱动
Class.forName("com.mysql.jdbc.Driver")
//加载Oracle驱动
Class.forName("oracle.jdbc.driver.OracleDriver")

2. 获得数据库连接：

DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/admin4j", "root", "root");

3.创建Statement\PreparedStatement对象：

conn.createStatement();
conn.prepareStatement(sql);

4.执行sql

execute()

完整实例

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;public class DbUtil {public static final String URL = "jdbc:mysql://localhost:3306/admin4j";public static final String USER = "admin4j";public static final String PASSWORD = "123456";public static void main(String[] args) throws Exception {//1.加载驱动程序Class.forName("com.mysql.jdbc.Driver");//2. 获得数据库连接Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);//3.操作数据库，实现增删改查Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT user_name, age FROM admin4j_goddess");//如果有数据，rs.next()返回truewhile(rs.next()){System.out.println(rs.getString("user_name")+" 年龄："+rs.getInt("age"));}}
}

增删改查

//定义工具类
public class DbUtil {public static final String URL = "jdbc:mysql://localhost:3306/admin4j";public static final String USER = "liulxadmin4jpublic static final String PASSWORD = "123456";private static Connection conn = null;static{try {//1.加载驱动程序Class.forName("com.mysql.jdbc.Driver");//2. 获得数据库连接conn = DriverManager.getConnection(URL, USER, PASSWORD);} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}}public static Connection getConnection(){return conn;}
}//定义实体
public class Goddess {private Integer id;private String user_name;private Integer sex;private Integer age;private Date birthday; //注意用的是java.util.Dateprivate String email;private String mobile;private String create_user;private String update_user;private Date create_date;private Date update_date;private Integer isDel;//getter setter方法。。。
}

DAO 层

public class GoddessDao {//增加public void addGoddess(Goddess g) throws SQLException {//获取连接Connection conn = DbUtil.getConnection();//sqlString sql = "INSERT INTO admin4j_goddess(user_name, sex, age, birthday, email, mobile,"+"create_user, create_date, update_user, update_date, isdel)"+"values("+"?,?,?,?,?,?,?,CURRENT_DATE(),?,CURRENT_DATE(),?)";//预编译PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行//传参ptmt.setString(1, g.getUser_name());ptmt.setInt(2, g.getSex());ptmt.setInt(3, g.getAge());ptmt.setDate(4, new Date(g.getBirthday().getTime()));ptmt.setString(5, g.getEmail());ptmt.setString(6, g.getMobile());ptmt.setString(7, g.getCreate_user());ptmt.setString(8, g.getUpdate_user());ptmt.setInt(9, g.getIsDel());//执行ptmt.execute();}public void updateGoddess(){//获取连接Connection conn = DbUtil.getConnection();//sql, 每行加空格String sql = "UPDATE admin4j_goddess" +" set user_name=?, sex=?, age=?, birthday=?, email=?, mobile=?,"+" update_user=?, update_date=CURRENT_DATE(), isdel=? "+" where id=?";//预编译PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行//传参ptmt.setString(1, g.getUser_name());ptmt.setInt(2, g.getSex());ptmt.setInt(3, g.getAge());ptmt.setDate(4, new Date(g.getBirthday().getTime()));ptmt.setString(5, g.getEmail());ptmt.setString(6, g.getMobile());ptmt.setString(7, g.getUpdate_user());ptmt.setInt(8, g.getIsDel());ptmt.setInt(9, g.getId());//执行ptmt.execute();}public void delGoddess(){//获取连接Connection conn = DbUtil.getConnection();//sql, 每行加空格String sql = "delete from admin4j_goddess where id=?";//预编译SQL，减少sql执行PreparedStatement ptmt = conn.prepareStatement(sql);//传参ptmt.setInt(1, id);//执行ptmt.execute();}public List<Goddess> query() throws SQLException {Connection conn = DbUtil.getConnection();Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("SELECT user_name, age FROM admin4j_goddess");List<Goddess> gs = new ArrayList<Goddess>();Goddess g = null;while(rs.next()){g = new Goddess();g.setUser_name(rs.getString("user_name"));g.setAge(rs.getInt("age"));gs.add(g);}return gs;}public Goddess get(){Goddess g = null;//获取连接Connection conn = DbUtil.getConnection();//sql, 每行加空格String sql = "select * from  admin4j_goddess where id=?";//预编译SQL，减少sql执行PreparedStatement ptmt = conn.prepareStatement(sql);//传参ptmt.setInt(1, id);//执行ResultSet rs = ptmt.executeQuery();while(rs.next()){g = new Goddess();g.setId(rs.getInt("id"));g.setUser_name(rs.getString("user_name"));g.setAge(rs.getInt("age"));g.setSex(rs.getInt("sex"));g.setBirthday(rs.getDate("birthday"));g.setEmail(rs.getString("email"));g.setMobile(rs.getString("mobile"));g.setCreate_date(rs.getDate("create_date"));g.setCreate_user(rs.getString("create_user"));g.setUpdate_date(rs.getDate("update_date"));g.setUpdate_user(rs.getString("update_user"));g.setIsDel(rs.getInt("isdel"));}return g;}
}

Statement和PreparedStatement的异同点

联系：

• 1、PreparedStatement继承自Statement

• 2、PrerapedStatement和Statement都是接口

• 3、PreParedStatement和Statement都可以实现对数据表的CRUD操作：增删改查

• 4、preparedstatement和statement两者都是用来执行sql查询语句的API之一。

区别：

1、statement不对sql语句做处理，直接交给数据库；而prepraedstatement是支持预编译的，会将编译好的sql语句放在数据库端，相当于缓存，对于多次重复执行的sql语句，使用prepraedstatement可以使得代码的执行效率更高。
2、使用 Statement 对象。在对数据库只执行一次性存取的时侯，用 Statement 对象进行处理。PreparedStatement 对象的开销比Statement大，对于一次性操作并不会带来额外的好处。
3、statement每次执行sql语句，相关数据库都要执行sql语句的编译，preparedstatement预编译得, preparedstatement支持批处理 。
4、执行许多SQL语句的JDBC程序产生大量的Statement和PreparedStatement对象。通常认为PreparedStatement对象比Statement对象更有效,特别是如果带有不同参数的同一SQL语句被多次执行的时候。PreparedStatement对象允许数据库预编译SQL语句，这样在随后的运行中可以节省时间并增加代码的可读性。
5、 PreparedStatement 可以规避 Statement弊端：①拼串 ②sql注入问题
6、PreparedStatement 可以实现操作Blob类型、Clob类型的数据