MySQL学习笔记_03

来源：互联网发布：java swing开发实例编辑：程序博客网时间：2024/06/07 17:59

1 JDBC入门

1.1 回顾

之前操作mysql数据库：

1）使用mysql客户端工具

2）使用客户端连接mysql服务器

3）发送sql语句到mysql服务器，执行

1.2 什么是JDBC？

使用java程序发送sql语句到数据库服务器端执行，这叫用到了JDBC技术！！！！

dbc是Oracle-Sun公司设计的一套专门用于java程序操作数据库的接口。

1.3 使用jdbc发送sql条件

连接mysql数据库：

数据库主机

端口

数据库用户名

数据库密码

连接的数据库

1.4JDBC的核心API

Connection接口：与具体的数据库的连接对象。

StatementcreateStatement() 创建一个静态sql语句对象

PreparedStatementprepareStatement(String sql) 创建预编译的sql语句对象

CallableStatementprepareCall(String sql) 创建存储过程的sql语句对象

Driver接口：数据库驱动程序的接口，所有具体数据库厂商需要的驱动程序需要实现此接口。

Connectionconnect(String url, Properties info) 用于获取数据库连接

案例：演示三种连接数据库的方式

在写程序之前，先要导入jar包；

步骤：

1.在project中创建一个文件夹lib

2.把jar包复制放入lib

3.在要导入的jar包点右键，build path --> Configure Build Path

public class DriverDemo {private static String url = "jdbc:mysql://localhost:3306/homework";private static String user = "root";private static String password = "root";public static void main(String[] args) throws Exception {conn1();conn2();conn3();}private static void conn3() throws Exception {//mysql驱动程序的driver实现类对象的静态代码块中已经注册了驱动//将Driver实现类对象加载到内存中，即可以完成注册Class.forName("com.mysql.jdbc.Driver");//获取连接Connection conn = DriverManager.getConnection(url, user, password);System.out.println(conn);}private static void conn2() throws Exception {Driver driver = new com.mysql.jdbc.Driver();//注册驱动DriverManager.registerDriver(driver);//获取链接Connection conn = DriverManager.getConnection(url, user, password);System.out.println(conn);}private static void conn1() throws Exception {//创建一个驱动类对象DriverDriver driver = new com.mysql.jdbc.Driver();Properties prop = new Properties();prop.setProperty("user", user);prop.setProperty("password", password);Connection conn = driver.connect(url, prop);System.out.println(conn);}}

Statement接口：用于执行静态 SQL 语句

nt executeUpdate(String sql) 执行更新操作的sql语句（create/alter/drop) DDL语句

public class Demo1 {private static String url = "jdbc:mysql://localhost:3306/homework";private static String user = "root";private static String password = "root";public static void main(String[] args) {Connection conn = null;Statement stmt = null;try {//注册驱动Class.forName("com.mysql.jdbc.Driver");//获取连接conn = DriverManager.getConnection(url, user, password);//创建语句执行者stmt = conn.createStatement();String sql = "CREATE TABLE student("+"id INT,"+"NAME VARCHAR(20),"+"age INT"+");";//执行sql语句int count = stmt.executeUpdate(sql);System.out.println(count);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}}

(insert/update/delete）DML语句

为了使代码更加简洁，我们先写一个JDBCUtil类，供我们使用

public class JDBCUtil {private static String url = "jdbc:mysql://localhost:3306/homework";private static String user = "root";private static String password = "root";static{try {Class.forName("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}}//获取连接public static Connection getConn(){try {Connection conn = DriverManager.getConnection(url, user, password);return conn;} catch (Exception e) {e.printStackTrace();throw new RuntimeException();}}//释放资源public static void close(Connection conn,Statement stmt,ResultSet rs){if(conn!=null){try {conn.close();} catch (SQLException e) {e.printStackTrace();}}if(stmt!=null){try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}if(rs!=null){try {rs.close();} catch (SQLException e) {e.printStackTrace();}}}}

DML语句演示：

public class Demo2 {public static void main(String[] args) {testInsert();testUpdate();testDelete();}private static void testDelete() {Connection conn = null;Statement stmt = null;try {//获取连接对象conn = JDBCUtil.getConn();//定义sqlString sql = "delete from student where id=1";//获取语句执行者stmt = conn.createStatement();int count = stmt.executeUpdate(sql);System.out.println(count);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();throw new RuntimeException();}finally{JDBCUtil.close(conn, stmt, null);}}private static void testUpdate() {Connection conn = null;Statement stmt = null;try {//创建连接对象conn = JDBCUtil.getConn();//定义sqlString sql = "update student set name='Hins Chueng' where id=1";//获取语句执行者stmt = conn.createStatement();int count = stmt.executeUpdate(sql);System.out.println(count);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();throw new RuntimeException();}finally{//释放资源JDBCUtil.close(conn, stmt, null);}}private static void testInsert(){Connection conn = null;Statement stmt = null;//获取连接对象try {conn = JDBCUtil.getConn();//定义sqlString sql = "insert into student values(1,'Hins',33)";//获取语句执行者stmt = conn.createStatement();int count = stmt.executeUpdate(sql);System.out.println(count);} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();throw new RuntimeException();}finally{JDBCUtil.close(conn, stmt, null);}}}

ResultSet executeQuery(String sql) 执行查询操作的sql语句(select)（DQL查询语句）

演示：

public class Demo3 {public static void main(String[] args) {Connection conn = null;Statement stmt = null;ResultSet rs = null;try {//获取连接对象conn = JDBCUtil.getConn();//定义sqlString sql = "select * from student";//获取语句执行者对象stmt = conn.createStatement();rs = stmt.executeQuery(sql);while(rs.next()){System.out.println(rs.getInt("ID")+"--"+rs.getString("name")+"--"+rs.getInt("age"));}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{JDBCUtil.close(conn, stmt, rs);}}}

这里executeQuery()的返回值是ResultSet （类似迭代器），通过ResultSet.next()判断是否存在数据，Result.getInt(),或者get其他类型的数据，传入改字段的名称，即可返回该该行对应的查询值；

PreparedStatement接口：用于执行预编译的 SQL语句（是Statement的子接口）

intexecuteUpdate() 执行更新操作的sql语句

ResultSetexecuteQuery() 执行查询操作的sql语句

演示：

public class Demo {public static void main(String[] args) {//testInsert();//testUpdate();//testDelete();testSelect();}private static void testSelect() {Connection conn = null;PreparedStatement stmt = null;ResultSet rs = null;try {conn = JDBCUtil.getConn();String sql = "select * from student where id=?;";stmt = conn.prepareStatement(sql);stmt.setInt(1, 1);rs = stmt.executeQuery();while (rs.next()) {System.out.println(rs.getInt("id")+"--"+rs.getString("name")+"--"+rs.getInt("age"));}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally{JDBCUtil.close(conn, stmt, rs);}}private static void testDelete() {Connection conn = null;PreparedStatement stmt = null;try {//创建连接对象conn = JDBCUtil.getConn();//定义sqlString sql = "delete from student where id=?;";//创建语句执行者对象stmt = conn.prepareStatement(sql);stmt.setInt(1, 2);stmt.executeUpdate();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally{JDBCUtil.close(conn, stmt, null);}}private static void testUpdate() {Connection conn = null;PreparedStatement stmt = null;try {//创建连接对象conn = JDBCUtil.getConn();//定义sqlString sql = "update student set name=? where id=?";//创建语句执行者对象stmt = conn.prepareStatement(sql);stmt.setString(1, "Jacky Chueng");stmt.setInt(2,2);stmt.executeUpdate();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally{JDBCUtil.close(conn, stmt, null);}}private static void testInsert() {Connection conn = null;PreparedStatement stmt = null;try {//创建连接对象conn = JDBCUtil.getConn();//定义sqlString sql = "insert into student values(?,?,?);";//创建语句执行者对象stmt =  conn.prepareStatement(sql);stmt.setInt(1, 2);stmt.setString(2, "Hins Chueng");stmt.setInt(3, 33);stmt.executeUpdate();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally{JDBCUtil.close(conn, stmt, null);}}}

利用数据库完成登陆案例：

public class Login {private static String user = "uu";private static String password = "123456";public static void main(String[] args) {//用Statement做Login1();//用PreparedStatement做Login2();}private static void Login2() {Connection conn = null;PreparedStatement stmt = null;ResultSet rs = null;try {conn = JDBCUtil.getConn();String sql = "select * from user where name=? and password=?";stmt = conn.prepareStatement(sql);stmt.setString(1, user);stmt.setString(2, password);rs = stmt.executeQuery();if(rs.next()){System.out.println("登陆成功");}else{System.out.println("登陆失败");}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{JDBCUtil.close(conn, stmt, rs);}}private static void Login1() {Connection conn = null;Statement stmt = null;ResultSet rs = null;try {//创建连接对象conn = JDBCUtil.getConn();//设置sql语句String sql = "select * from user where name='"+user+"' and password='"+password+"'";//创建语句执行者stmt = conn.createStatement();rs = stmt.executeQuery(sql);if(rs.next()){System.out.println("登陆成功");}else{System.out.println("登陆失败");}} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{JDBCUtil.close(conn,stmt,rs);}}}

这里使用了PreparedStatement 和 Statement 两种方法来完成这个功能，但是如果使用Statement 验证登陆的话，会出现不安全的情况，就是在修改输入的用户名，在用户名后加入 ' OR 1=1 -- ，就会使代码恒成立，即当user表中有数据时，即使用户名和密码都错误，也可以完成登陆；

CallableStatement接口：用于执行 SQL 存储过程的接口（是PreparedStatement的子接口）

ResultSet executeQuery() 执行存储过程的sql语句

案例演示：

public class Demo {public static void main(String[] args) {//执行带输入输出参数的存储过程test();}private static void test() {// TODO Auto-generated method stubConnection conn = null;CallableStatement stmt = null;ResultSet rs = null;try {conn = JDBCUtil.getConn();String sql = "call Pro_NameById(?,?);";stmt = conn.prepareCall(sql);//给问号赋值stmt.setInt(1, 1);//如果存储过程带有输出参数的时候，首先需要注册，输出参数的类型stmt.registerOutParameter(2, java.sql.Types.VARCHAR);//发送并执行SQLstmt.executeQuery();System.out.println(stmt.getString(2));} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally{JDBCUtil.close(conn, stmt, rs);}}}

ResultSet接口：结果集对象。存储所有数据库查询的结果，用该对象进行数据遍历。

booleannext() ：把光标移动到下一行。如果下一行有数据，返回true，如果没有下一行数据，返回false。

getXXX（列索引|列字段名称）：获取字段的数据

图解：

使用方法类似迭代器，获取数据时时通过getInt getBoolean 等方法获取该字段数据；

阅读全文

0 0