数据库驱和JDBC
数据库驱动
顾名思义，数据库驱动是应用程序和数据库存储之间的一种接口，数据库厂商为了某一种
开发语言环境（比如Java，C）能够实现数据库调用而开发的类似翻译员功能的程序，将复杂
的数据库操作与通信抽象成为了当前开发语言的访问接口。
JDBC
SUN公司为了简化开发人员的操作，提供了一个（Java操作数据库）的规范，俗称JDBC。
这些规范的实现由具体的厂商去做~
需要导入一个数据库驱动包，数据库什么版本就下什么版本，我们这里是5.1.47的驱动
第一个JDBC程序
1.创建测试数据库
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci; USE jdbcStudy; CREATE TABLE `users`( id INT PRIMARY KEY, NAME VARCHAR(40), PASSWORD VARCHAR(40), email VARCHAR(60), birthday DATE ); INSERT INTO `users`(id,NAME,PASSWORD,email,birthday) VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'), (2,'lisi','123456','lisi@sina.com','1981-12-04'), (3,'wangwu','123456','wangwu@sina.com','1979-12-04')
2.创建一个普通项目
3.导入数据库驱动
新建lib文件夹，将驱动的jar包放进去，并add library
4.编写测试代码
//我的第一个JDBC程序 public class jdbcFirstDemo { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1.加载驱动 Class.forName("com.mysql.jdbc.Driver");//固定写法 //2.用户信息 String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true"; String userName="root"; String password="123456"; //3.连接成功，数据库对象,代表数据库 Connection connection = DriverManager.getConnection(url, userName, password); //4.执行SQL的对象 Statement statement = connection.createStatement(); //5.执行SQL String sql="select * from users"; ResultSet resultSet = statement.executeQuery(sql); while (resultSet.next()){ System.out.println("id="+resultSet.getObject("id")); System.out.println("name="+resultSet.getObject("NAME")); System.out.println("pwd="+resultSet.getObject("PASSWORD")); System.out.println("email="+resultSet.getObject("email")); System.out.println("birth="+resultSet.getObject("birthday")); System.out.println("=============================="); } //6.释放连接 resultSet.close(); statement.close(); connection.close(); } }
statement对象
JDBC中statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库
发送增删改查语句即可。
statement对象的executeUpdate方法，用于向数据库发送增删改的SQL语句，executeUpdate执行完成后，将会返回
一个整数（即增删改语句导致了数据库j几行数据发生了变化）
statement对象的executeQuery方法，用于向数据库发送查询语句，executeQuery方法返回代表查询结果的resultSet对象
1.CRUD操作-insert
Statement st = conn.createStatement(); String sql = "insert into user(...) values(...)"; int num = st.executeUpdate(sql); if(num > 0){ System.out.println("插入成功！"); }
2.CRUD操作-delete
Statement st = conn.createStatement(); String sql = "delete from user where id=1"; int num = st.executeUpdate(sql); if(num > 0){ System.out.println("删除成功！"); }
3.CRUD操作-update
Statement st = conn.createStatement(); String sql = "update user set name = '..' where name = '..'; int num = st.executeUpdate(sql); if(num > 0){ System.out.println("修改成功！"); }
4.CRUD操作-select
Statement st = conn.createStatement(); String sql = "select * from user where id = 1"; ResultSet rs = st.executeQuery(sql); while(rs.next){ System.out.println(rs.getObject("id")); .... }
提取工具类
public class JdbcUtils { private static String driver =null; private static String url =null; private static String username =null; private static String password =null; static { try{ InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(in); driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); //1.加载驱动只用加载一次 Class.forName(driver); }catch (Exception e){ e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,username,password); } //释放资源 public static void release(Connection conn, Statement st, ResultSet rs){ if (rs!=null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (rs!=null){ try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn!=null){ try { rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }
INSERT
public class TestInsert { public static void main(String[] args) { Connection conn = null; Statement st =null; ResultSet rs = null; try { conn = JdbcUtils.getConnection();//获取数据库连接 st = conn.createStatement(); String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,birthday)"+ "values(4,'xiaolei','33312','34242@qq.com','2022-02-02')"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("插入成功！！！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } }
UPDATE
public class TestUpdate { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs= null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); String sql = "update users set name = '憨憨' where name = 'wangwu'"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("更新成功！！！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } }
DELETE
public class TestDelete { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); String sql = "delete from users where id =4"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("删除成功！！！"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } }
SELECT
public class TestSelect { public static void main(String[] args) { Connection conn = null; Statement st= null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); String sql = "select * from users"; rs = st.executeQuery(sql); while (rs.next()){ System.out.println("id="+rs.getObject("id")); System.out.println("name="+rs.getObject("NAME")); System.out.println("password="+rs.getObject("PASSWORD")); System.out.println("email="+rs.getObject("email")); System.out.println("birthday="+rs.getObject("birthday")); System.out.println("+++++++++++++++++++++++"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } }
