JDBC是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。
基础面试有时候可会有JDBC链接数据库这么一道题。下面是JDBC的简单操作例子,以mysql为例:
新建数据库
test
新建表userCREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
导入依赖包
Mysql驱动jar包:
mysql.jar
简单curdpackage com.weizhixi.test;
import java.sql.*;
public class Jdbc{
public static void main(String[] args){
//addUser("user");
//updateUser(1,"user1");
findAllUser();
//findUserById(1);
//deleteUser(1);
}
//获取数据库连接Connection对象
public static Connection getConnection() throws ClassNotFoundException,SQLException{
//注册JDBC驱动程序:mysql
Class.forName("com.mysql.jdbc.Driver");
//打开一个连接,test库,账号root,密码root
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","root");
return con;
}
//查询所有用户
public static void findAllUser(){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
//打开连接
con = getConnection();
//SQL语句
String sql = "select * from user";
//SQL语句的对象。效率更高,并且可以防止SQL注入操作。
pstmt = con.prepareStatement(sql);
//执行查询,返回结果集
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println("ID:"+rs.getInt(1) +" 姓名:"+rs.getString(2));
}
}catch(Exception e){
e.printStackTrace();
}finally{
close(con, pstmt, rs);
}
}
//根据ID查询用户
public static void findUserById(int id){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
con = getConnection();
String sql = "select * from user where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1,id);
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println("ID:"+rs.getInt(1) +" 姓名:"+rs.getString(2));
}
}catch(Exception e){
e.printStackTrace();
}finally{
close(con, pstmt, rs);
}
}
//修改用户
public static void updateUser(int id, String name){
Connection con = null;
PreparedStatement pstmt = null;
try{
con = getConnection();
String sql = "update user set name=? where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1,name);
pstmt.setInt(2,id);
//执行后的更新行数; 返回0表示SQL语句没有执行成功
int res = pstmt.executeUpdate();
System.out.println(res);
}catch(Exception e){
e.printStackTrace();
}finally{
close(con, pstmt, null);
}
}
//新增用户
public static void addUser(String name){
Connection con = null;
PreparedStatement pstmt = null;
try{
con = getConnection();
String sql = "insert into user(name) values(?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1,name);
int res = pstmt.executeUpdate();
System.out.println(res);
}catch(Exception e){
e.printStackTrace();
}finally{
close(con, pstmt, null);
}
}
//删除用户
public static void deleteUser(int id){
Connection con = null;
PreparedStatement pstmt = null;
try{
con = getConnection();
String sql = "delete from user where id=?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1,id);
int res = pstmt.executeUpdate();
System.out.println(res);
}catch(Exception e){
e.printStackTrace();
}finally{
close(con, pstmt, null);
}
}
//释放资源
public static void close(Connection con, PreparedStatement pstmt, ResultSet rs){
if(rs!=null){
try{
rs.close();
}catch(SQLException e){
e.printStackTrace();
}
}
if(pstmt!=null){
try{
pstmt.close();
}catch(SQLException e){
e.printStackTrace();
}
}
if(con!=null){
try{
con.close();
}catch(SQLException e){
e.printStackTrace();
}
}
}
}
maven依赖
如果项目是maven,直接使用(版本自己换)
mysql
mysql-connector-java
5.1.13
其他驱动配置com.microsoft.jdbc.sqlserver.SQLServerDriver
jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=test"
oracle.jdbc.driver.OracleDriver
jdbc:oracle:thin:@localhost:1521:test
com.mysql.jdbc.Driver
jdbc:mysql://localhost:3306/test
可能遇到异常
请导入相应数据库驱动jar包,如mysql.jarjava.lang.ClassNotFoundException: com.mysql.jdbc.Driver
at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
at java.lang.ClassLoader.loadClass(ClassLoader.java:425)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:308)
at java.lang.ClassLoader.loadClass(ClassLoader.java:358)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:190)
at com.weizhixi.test.Jdbc.getConnection(Jdbc.java:19)
at com.weizhixi.test.Jdbc.findAllUser(Jdbc.java:29)
at com.weizhixi.test.Jdbc.main(Jdbc.java:8)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)
原创文章,转载请注明出处:https://www.weizhixi.com/article/46.html