前言
使用java对数据库进行查询、插入、删除,更新。
要使用mysql创建一个名为user的表
以下是本篇文章正文内容,下面案例可供参考
一、查询数据库名称和数据库版本
代码如下(示例):
package jdbc;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
public class example01 {
public static void main(String[] args) {
Connection conn=null;
try {
//1.加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//2.创建数据库
String url="jdbc:mysql://localhost:3306/java";
String username="root";
String password=null;
conn=DriverManager.getConnection(url,username,password);
//获取数据库信息
DatabaseMetaData metaData = conn.getMetaData();//局部变量,返回一个data.base
System.out.println(metaData.getDatabaseProductName());
System.out.println(metaData.getDatabaseProductVersion());
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
if(conn!=null){
try {
//3.关闭数据库连接
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
二、插入、查询、删除、更新
1.插入
1.利用statement进行插入代码如下(示例):
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class example02 {
public static void main(String[] args) {
Connection conn=null;
Statement stmt=null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/java";//localhost是数据库java是数据库名字
String username="root";
String password=null;
conn=DriverManager.getConnection(url,username,password);
stmt=conn.createStatement();
//参数 sql 返回值 影响记录条数
String sql="insert into user"+
"(username,password,name,sex,classes,phone,email)"
+"value('test','test','test',null,null,null,null)";
int result=stmt.executeUpdate(sql);
System.out.println(result);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
//Resultset statement Connnection
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
}
}
2.利用PrepareStatement进行插入代码如下(示例):
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class example03 {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement pstmt=null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/java?useUnicode=true&characterEncoding=UTF-8";
String name="root";
String password=null;
conn=DriverManager.getConnection(url,name,password);
String sql="insert into user"+
"(username,password,name,sex)"+
"values(?,password(?),?,'男')";
pstmt=conn.prepareStatement(sql);
//1.按照顺序
//2.所有的参数必须赋值
//3.索引从1开始
//4.所有的数据类型都可以用setstring和setobjection
pstmt.setString(1, "john");
pstmt.setString(2, "john");
pstmt.setString(3, "约翰");
int result=pstmt.executeUpdate();
System.out.println(String.format("数据库保存了%d个用户", result));
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
}
}
2.查询
3.查询代码如下(示例):
//进行查询时需要准备的类
package jdbc;
public class User {
private Integer id;
private String username;
private String password;
private String name;
private String sex;
private String classes;
private String email;
private String phone;
//写好成员函数后,右键-》选择resoure-》选择genarate Getters and Setters
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getClasses() {
return classes;
}
public void setClasses(String classes) {
this.classes = classes;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class example06 {
public static void main(String[] args) {
example06 e=new example06();
User user=e.get(1);
System.out.println(user.getId()+" "+user.getUsername()+" "+user.getName()+" "+user.getSex()+" "+user.getEmail()+" "
+user.getPhone());
}
public User get(Integer id){
User user=null;
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
//1.加载驱动类
Class.forName("com.mysql.jdbc.Driver");
//2.创建数据库连接
String url="jdbc:mysql://localhost:3306/java?useUnicode=true&characterEcoding=UTF-8";//数据库地址useUnicode=true&characterEncoding=UTF-8是为了防止中文乱码
String username="root";//登录名
String password=null;//密码
conn=DriverManager.getConnection(url,username,password);
//3.创建声明
String sql="select id ,username,name,sex,classes,email,phone"+
" from user"+//这里要加空格
" where id=?";//这里要加空格
pstmt=conn.prepareStatement(sql);
//4.执行查询
pstmt.setInt(1, id);
rs = pstmt.executeQuery();
//5.处理结果集,资源关闭之前,要把结果集里面的数据取出来
if(rs.next()){
user =new User();
// Integer id1=rs.getInt(1);
// 建议按照顺序,1,3,4 对 1 4 3 错
// 所有的值都可以getString getObject
Integer id1=rs.getInt("id");
user.setId(id1);
user.setUsername(rs.getString(2));
user.setName(rs.getString(3));
user.setSex(rs.getString(4));
user.setClasses(rs.getString(5));
user.setEmail(rs.getString(6));
user.setPhone(rs.getString(7));
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally{
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return user;
}
}
/*
* java bean
* 1) 有一定规范的普通的java类java bean
* 2) BJB: enterprice java bean
* 3) java bean
* 规范
* 1) 属性都是private,setter getter
* Username setUsername getUsername
* boolean is代替get state isState
* 2) 必须有一个无参数的构造方法
* new User()
* 3) 所有的getter,setter方法必须是public
*
*/
3.更新
4.更新代码如下(示例):
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class example04 {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement pstmt=null;
try {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/java?useUnicode=true&charaterEcoding=UTF-8";//数据库地址useUnicode=true&characterEncoding=UTF-8是为了防止中文乱码
String username="root";//登录名
String password=null;//密码
conn= DriverManager.getConnection(url,username,password);
String sql="update user set password=password(?) where id=?";
pstmt= conn.prepareStatement(sql);
pstmt.setString(1,"love");
pstmt.setString(2,"4");
int result= pstmt.executeUpdate();
System.out.println(String.format("更新成功%d",result));
} catch (Exception e) {
e.printStackTrace();
} finally {
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
4.删除
5.删除代码如下(示例):
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class example03 {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement pstmt=null;
try {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/java?useUnicode=true&characterEcoding=UTF-8";//数据库地址useUnicode=true&characterEncoding=UTF-8是为了防止中文乱码
String username="root";//登录名
String password=null;//密码
conn= DriverManager.getConnection(url,username,password);
String sql="delete from user"+
" where username like concat('%',?,'%')";
pstmt= conn.prepareStatement(sql);
pstmt.setString(1,"john");
int result=pstmt.executeUpdate();
System.out.println(String.format("数据库受影响的数据有%d条",result));
} catch (Exception e) {
e.printStackTrace();
} finally {
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(pstmt!=null){
try {
pstmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
总结
数据库的增删改查如上
mysql软件以及要用的jar包
链接: mysql-connector-java-5.1.29-bin.jar
提取码:b9do
复制这段内容后打开百度网盘手机App,操作更方便哦–来自百度网盘超级会员V1的分享.
链接: Navcat Premium 11.0.17.zip
提取码:g02h
复制这段内容后打开百度网盘手机App,操作更方便哦–来自百度网盘超级会员V1的分享)
链接: mysql-5.5.16-win32.msi
提取码:cv83
复制这段内容后打开百度网盘手机App,操作更方便哦–来自百度网盘超级会员V1的分享fg )