开发步骤
1.注册驱动
告知JVM使用的是哪一个数据库的驱动
2.获得连接
使用JDBC中的类,完成对MySQL数据库的连接
3.获得语句执行平台
通过连接对象获取对SQL语句的执行者对象
4.执行sql语句
使用执行者对象,向数据库执行SQL语句
获取到数据库的执行后的结果
5.处理结果
6.释放资源 一堆close()
导入mysql数据库驱动程序jar包
* 创建lib目录，用于存放当前项目需要的所有jar包
* 选择jar包，右键执行build path / Add to Build Path
增操作
public static void main(String[] args) throws Exception{
//加载驱动 告知JVM使用的是哪一个数据库的驱动
Class.forName("com.mysql.jdbc.Driver");
//获得数据库连接对象 使用JDBC中的类,完成对MySQL数据库的连接
String url="jdbc:mysql://localhost:3306/mybase?useUnicode=true&characterEncoding=UTF-8";
String username ="root";
String password="root";
Connection conn= DriverManager.getConnection(url,username,password);
//获得语句执行平台，通过连接对象获取对SQL语句的执行者对象
//增加操作
PreparedStatement preparedStatement=conn.prepareStatement("insert sort ()VALUES ()");
preparedStatement.setInt(1,500);
preparedStatement.setString(2,"haha");
preparedStatement.executeUpdate();
preparedStatement.close();
conn.close();
}
}
删除操作
public static void main(String[] args) throws Exception{
//加载驱动 告知JVM使用的是哪一个数据库的驱动
Class.forName("com.mysql.jdbc.Driver");
//获得数据库连接对象 使用JDBC中的类,完成对MySQL数据库的连接
String url="jdbc:mysql://localhost:3306/mybase?useUnicode=true&characterEncoding=UTF-8";
String username ="root";
String password="root";
Connection conn= DriverManager.getConnection(url,username,password);
//获得语句执行平台，通过连接对象获取对SQL语句的执行者对象
PreparedStatement preparedStatement=conn.prepareStatement("delete from sort where sid=?");
preparedStatement.setInt(1,12);//sid什么类型就set什么类型，前面的1代表第几个问号，
这样写为了防止SQL注入攻击
preparedStatement.executeUpdate();
preparedStatement.close();
conn.close();
}
更新操作
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
//获得数据库连接对象
String url="jdbc:mysql://localhost:3306/mybase?useUnicode=true&characterEncoding=UTF-8";
String username ="root";
String password="root";
Connection conn= DriverManager.getConnection(url,username,password);
//获取执行平台，相当于查询界面
PreparedStatement preparedStatement=conn.prepareStatement("update sort set sname = ?,sprice = ?, sdesc=? where sid = ?");
preparedStatement.setString(1,"图书");
preparedStatement.setDouble(2,985.6);
preparedStatement.setString(3,"看书吧");
preparedStatement.setInt(4,10);
//执行语句，运行了程序
int set=preparedStatement.executeUpdate();
preparedStatement.close();
conn.close();
preparedStatement.close();
}
查询操作,结果返回的是结果集
public static void main(String[] args) throws Exception{
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获得数据库连接对象
String url="jdbc:mysql://localhost:3306/mybase?useUnicode=true&characterEncoding=UTF-8";
String username ="root";
String password="root";
Connection conn= DriverManager.getConnection(url,username,password);
System.out.println("请输入用户名");
Scanner s=new Scanner(System.in);
String uname=s.nextLine();
System.out.println("请输入密码");
String pwd=s.nextLine();
//获取执行平台，相当于执行语句那个界面
PreparedStatement preparedStatement=conn.prepareStatement("select * from users where username = ? and password = ?");
preparedStatement.setString(1,uname);
preparedStatement.setString(2,pwd);
//执行Sql语句，结果返回个结果集
ResultSet set=preparedStatement.executeQuery();
//set.next()代表结果集合的第一行
if(set.next()==false)
{
System.out.println("登录失败");
}
else{
System.out.println("登陆成功");
}
conn.close();
preparedStatement.close();
}
习题
对数据库表Sort进行增删改查操作
1.首先定义一个类，让其变量和sort表的字段一样
public class sort {
@Override
public String toString() {
return "sort{" +
"sid=" + sid +
", sname='" + sname + '\'' +
", sdesc='" + sdesc + '\'' +
", sprice=" + sprice +
", sdate=" + sdate +
'}';
}
private int sid;
private String sname;
private String sdesc;
private double sprice;
private Date sdate;
public Date getSdate() {
return sdate;
}
public void setSdate(Date sdate) {
this.sdate = sdate;
}
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getSdesc() {
return sdesc;
}
public void setSdesc(String sdesc) {
this.sdesc = sdesc;
}
public double getSprice() {
return sprice;
}
public void setSprice(double sprice) {
this.sprice = sprice;
}
}
2.把连接数据库的重复的步骤抽象成一个类
public class JDBCUtils {
//2.获得数据库连接对象
static String url = "jdbc:mysql://localhost:3306/mybase?useUnicode=true&characterEncoding=UTF-8";
//要连接的数据库服务器的基本信息，包括（ip，端口，数据库名）
static String username = "root";
static String password = "root";
private static Connection conn = null;
static {
//1.加载mysql数据库驱动
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private JDBCUtils() {
}
public static Connection getConnection() throws Exception {
Connection conn = DriverManager.getConnection(url, username, password);
return conn;
}
public static void close(Statement statement, Connection connection) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet resultSet, Statement statement, Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
3.进行增删改查操作
public class JDBCFinalDemo {
//修改
static void updateSort(sort a) throws Exception {
Connection conn = JDBCUtils.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement
("update sort SET sname=?,sprice=?,sdesc=?,sdate=? where sid=?");
preparedStatement.setString(1, a.getSname());
preparedStatement.setDouble(2, a.getSprice());
preparedStatement.setString(3, a.getSdesc());
Date date = a.getSdate();
long time = date.getTime();
java.sql.Timestamp date1 = new java.sql.Timestamp(time);
preparedStatement.setTimestamp(4, date1);
preparedStatement.setInt(5, a.getSid());
preparedStatement.executeUpdate();
JDBCUtils.close(preparedStatement, conn);
}
//删除
static void deleteSort(int id) throws Exception {
Connection conn = JDBCUtils.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement("DELETE from sort where sid=?");
preparedStatement.setInt(1, id);
int row = preparedStatement.executeUpdate();
JDBCUtils.close(preparedStatement, conn);
}
//新增
static int insertSort(sort a) throws Exception {
Connection conn = JDBCUtils.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement("INSERT into sort (sname,sprice,sdesc,sdate)VALUES(?,?,?,?)");
preparedStatement.setString(1, a.getSname());
preparedStatement.setDouble(2, a.getSprice());
preparedStatement.setString(3, a.getSdesc());
Date date = a.getSdate();
long time = date.getTime();
java.sql.Timestamp sqlDate = new java.sql.Timestamp(time);
preparedStatement.setTimestamp(4, sqlDate);
int row = preparedStatement.executeUpdate();
JDBCUtils.close(preparedStatement, conn);
return row;
}
//查一个
static sort selectSort(int id) throws Exception {
Connection conn = JDBCUtils.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement("select * from sort where sid=?");
preparedStatement.setInt(1, id);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
int sid = resultSet.getInt("sid");
String sname = resultSet.getString("sname");
double dPrice = resultSet.getDouble("sprice");
String sdesc = resultSet.getString("sdesc");
Date sdate = resultSet.getTimestamp("sdate");
sort sort = new sort();
sort.setSid(sid);
sort.setSprice(dPrice);
sort.setSname(sname);
sort.setSdesc(sdesc);
sort.setSdate(sdate);
return sort;
} else {
return null;
}
}
//查全部
static List<sort> selectSortAll() throws Exception {
Connection conn = JDBCUtils.getConnection();
PreparedStatement preparedStatement = conn.prepareStatement("select * from sort ");
ResultSet resultSet = preparedStatement.executeQuery();
List<sort> list = new ArrayList<>();
while (resultSet.next()) {
int sid = resultSet.getInt("sid");
String sname = resultSet.getString("sname");
double dPrice = resultSet.getDouble("sprice");
String sdesc = resultSet.getString("sdesc");
Date sdate = resultSet.getTimestamp("sdate");
sort sort = new sort();
sort.setSid(sid);
sort.setSprice(dPrice);
sort.setSname(sname);
sort.setSdesc(sdesc);
sort.setSdate(sdate);
list.add(sort);
}
return list;
}
public static void main(String[] args) throws Exception {
//查一个
sort sort = selectSort(6);
System.out.println(sort);
//查全部
List list = selectSortAll();
System.out.println(list);
//新增
sort a = new sort();
Date d = new Date();
a.setSdate(d);
a.setSdesc("老好啦");
a.setSname("雪糕");
a.setSprice(20);
// int add=insertSort(a);
//删除
// deleteSort(13);
//修改
sort b = new sort();
Date d2 = new Date();
b.setSdate(d2);
b.setSprice(500);
b.setSdesc("不咋的");
b.setSname("馒头");
b.setSid(16);
updateSort(b);
}
}
