首页 > 代码库 > JDBC学习笔记：CRUD
JDBC学习笔记：CRUD
1.建立数据库
1 --创建数据库 2 create database jdbc; 3 4 --选择调用jdbc数据库 5 use jdbc; 6 7 --创建user表，id:设置为主键 8 create table user 9 (10 id integer not null auto_increment primary key,11 name varchar(45) not null,12 birthday Date,13 money float14 );15 16 --向表中插入数据17 insert into user(name,birthday,money)18 values(‘zhangsan‘, ‘1985-01-01‘, 100),19 (‘lisi‘, ‘1986-01-01‘, 200),20 (‘wangwu‘, ‘1987-01-01‘, 300);
2.JDBC操作的基本步骤
(1)加载驱动：只需加载一次
(2)建立连接：建立与数据库的连接，DriverManager.getConnection(url,username,password); url格式：jdbc:协议名称:子名称//主机地址:端口号/数据库名称 username：数据库用户名 password：密码
(3)创建语句
(4)执行语句
(5)处理执行结果
(6)关闭连接，释放资源
1 import java.sql.Connection; 2 import java.sql.DriverManager; 3 import java.sql.ResultSet; 4 import java.sql.SQLException; 5 import java.sql.Statement; 6 7 public class Demo { 8 9 public static void main(String[] args) throws ClassNotFoundException, SQLException {10 // 加载驱动11 Class.forName("com.mysql.jdbc.Driver");12 13 // 建立连接 url格式 - jdbc:子协议:子名称//主机名:端口/数据库名？属性名=属性值&…14 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc","root","1234");15 16 // 创建语句 17 Statement st = conn.createStatement();18 19 // 执行语句20 ResultSet rs = st.executeQuery("select * from user");21 22 // 处理结果，打印user表中的id字段23 while(rs.next()) {24 System.out.println(rs.getInt("id"));25 }26 27 //关闭连接，释放资源28 rs.close();29 st.close();30 conn.close();31 }32 }
【运行结果】：
1 zhangsan
2 lisi
3 wangwu
3.自建工具包
(1)创建连接和释放资源的工具包
1 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.sql.Statement; 8 9 public final class JdbcUtils {10 11 private static String url = "jdbc:mysql://localhost:3306/jdbc";12 private static String user = "root";13 private static String password = "1234";14 15 private JdbcUtils(){}16 17 static {18 try {19 Class.forName("com.mysql.jdbc.Driver");20 } catch (ClassNotFoundException e) {21 throw new ExceptionInInitializerError(e);22 }23 }24 25 public static Connection getConnection() throws SQLException {26 return DriverManager.getConnection(url, user, password);27 }28 29 public static void free(ResultSet rs, Statement st, Connection conn) {30 try {31 if (rs != null) {32 rs.close();33 }34 } catch (SQLException e) {35 e.printStackTrace();36 } finally {37 try {38 if (st != null) 39 st.close();40 } catch (SQLException e) {41 e.printStackTrace();42 } finally {43 if (conn != null)44 try {45 conn.close();46 } catch (SQLException e) {47 e.printStackTrace();48 } 49 }50 }51 }52 }
(2)利用单利设计模式创建的工具包，用于建立连接和释放资源
1
2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.sql.Statement; 8 9 public class JdbcUtilsSing {10 11 private String url = "jdbc:mysql://localhost:3306/jdbc";12 private String user = "root";13 private String password = "1234";14 15 private static JdbcUtilsSing instance = null;16 17 private JdbcUtilsSing() {18 19 }20 21 public static JdbcUtilsSing getInstance() {22 23 if (instance == null) {24 synchronized(JdbcUtilsSing.class) {25 if (instance == null)26 instance = new JdbcUtilsSing();27 } 28 }29 return instance;30 }31 32 static {33 try {34 Class.forName("com.mysql.jdbc.Driver");35 } catch (ClassNotFoundException e) {36 throw new ExceptionInInitializerError(e);37 }38 }39 40 public Connection getConnection() throws SQLException {41 return DriverManager.getConnection(url, user, password);42 }43 44 public void free(ResultSet rs, Statement st, Connection conn) {45 try {46 if (rs != null) {47 rs.close();48 }49 } catch (SQLException e) {50 e.printStackTrace();51 } finally {52 try {53 if (st != null) 54 st.close();55 } catch (SQLException e) {56 e.printStackTrace();57 } finally {58 if (conn != null)59 try {60 conn.close();61 } catch (SQLException e) {62 e.printStackTrace();63 } 64 }65 }66 }67 }
4.利用Junit测试CRUD操作
(1)create
1 @Test 2 public void create() throws SQLException { 3 Connection conn = null; 4 Statement st = null; 5 ResultSet rs = null; 6 try { 7 conn = JdbcUtils.getConnection(); 8 st = conn.createStatement(); 9 String sql = "insert into user(name,birthday,money) values(‘name 1‘,‘1987-01-01‘,400)";10 int i = st.executeUpdate(sql);11 System.out.println("i = " + i);12 } finally {13 JdbcUtils.free(rs, st, conn);14 }15 }
(2)read
1 @Test 2 public void read() throws SQLException { 3 Connection conn = null; 4 Statement st = null; 5 ResultSet rs = null; 6 try { 7 conn = JdbcUtils.getConnection(); 8 st = conn.createStatement(); 9 rs = st.executeQuery("select id,name,birthday,money from user");10 11 while(rs.next()) {12 System.out.println(rs.getInt("id") + "\t" + rs.getString("name") + "\t" 13 + rs.getDate("birthday"));14 }15 } finally {16 JdbcUtils.free(rs, st, conn);17 }18 }
(3)update
1 @Test 2 public void update() throws SQLException { 3 Connection conn = null; 4 Statement st = null; 5 ResultSet rs = null; 6 try { 7 conn = JdbcUtils.getConnection(); 8 st = conn.createStatement(); 9 String sql = "update user set money=money+100";10 int i = st.executeUpdate(sql);11 System.out.println("i = " + i);12 } finally {13 JdbcUtils.free(rs, st, conn);14 }15 }
(4)delete
1 @Test 2 public void delete() throws SQLException { 3 Connection conn = null; 4 Statement st = null; 5 ResultSet rs = null; 6 try { 7 conn = JdbcUtils.getConnection(); 8 st = conn.createStatement(); 9 String sql = "delete from user where id > 3";10 int i = st.executeUpdate(sql);11 System.out.println("i = " + i);12 } finally {13 JdbcUtils.free(rs, st, conn);14 }15 }
JDBC学习笔记：CRUD
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。