写在前面:
在用JDBC操作数据库时,需要有以下两点知识储备
1、mysql安装配置和基础的sql语句 2、java基础知识
JDBC介绍:
Java 数据库连接,(Java Database Connectivity,简称JDBC)是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口,提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。它JDBC是面向关系型数据库的。简单地说,就是用于执行SQL语句的一类Java API,通过JDBC使得我们可以直接使用Java编程来对关系数据库进行操作。通过封装,可以使开发人员使用纯Java API完成SQL的执行。
开始代码
1、下载并导入mysql连接的依赖包
2、创建一个实体与数据库中的一张表对应
数据库表名:student {字段:id,name,sex,age}
实体类:
public class Student { private String Id; private String Name; private String Sex; private String Age; Student(String Name, String Sex, String Age) { this.Id = null; //default this.Name = Name; this.Sex = Sex; this.Age = Age; } public String getId() { return Id; } ...//get\set方法... }
3、获取连接
private static Connection getConn() { String driver = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/samp_db"; String username = "root"; String password = ""; Connection conn = null; try { Class.forName(driver); //classLoader,加载对应驱动 conn = (Connection) DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; }
4、对应操作
(1)insert
private static int insert(Student student) { Connection conn = getConn(); int i = 0; String sql = "insert into students (Name,Sex,Age) values(?,?,?)"; PreparedStatement pstmt; try { pstmt = (PreparedStatement) conn.prepareStatement(sql); pstmt.setString(1, student.getName()); pstmt.setString(2, student.getSex()); pstmt.setString(3, student.getAge()); i = pstmt.executeUpdate(); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i; }
(2)update
private static int update(Student student) { Connection conn = getConn(); int i = 0; String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'"; PreparedStatement pstmt; try { pstmt = (PreparedStatement) conn.prepareStatement(sql); i = pstmt.executeUpdate(); System.out.println("resutl: " + i); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i; }
(3)select
以select * from XXX为例。
private static Integer getAll() { Connection conn = getConn(); String sql = "select * from students"; PreparedStatement pstmt; try { pstmt = (PreparedStatement)conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); int col = rs.getMetaData().getColumnCount();
System.out.println("============================"); while (rs.next()) { for (int i = 1; i <= col; i++) { System.out.print(rs.getString(i) + "\t"); if ((i == 2) && (rs.getString(i).length() < 8)) { System.out.print("\t"); } } System.out.println(""); } System.out.println("============================"); } catch (SQLException e) { e.printStackTrace(); } return null; }
(4)delete
private static int delete(String name) { Connection conn = getConn(); int i = 0; String sql = "delete from students where Name='" + name + "'"; PreparedStatement pstmt; try { pstmt = (PreparedStatement) conn.prepareStatement(sql); i = pstmt.executeUpdate(); System.out.println("resutl: " + i); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i; }
5、测试
public static void main(String args[]) { JDBCOperation.getAll(); JDBCOperation.insert(new Student("Achilles", "Male", "14")); JDBCOperation.getAll(); JDBCOperation.update(new Student("Bean", "", "7")); JDBCOperation.delete("Achilles"); JDBCOperation.getAll(); }
至此,JDBC连接mysql数据库并进行操作已经完成了,具体详细知识,小伙伴们可以参照java API进行深入学习!!