数据库
-- 建立数据库
CREATE DATABASE mydbtest;
-- 删除数据库
DROP DATABASE mydbtest;
-- 建立表格
CREATE TABLE t_product(
id INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20),
price INT,
createAddress VARCHAR(20),
createDate DATE
);
-- 插入数据
INSERT INTO t_product(pname,price,createAddress,createDate)
VALUES('电视机',1500,'四川','2017-03-14');
-- 查询全部
SELECT*FROM t_product;
-- 查询列为pname
SELECT pname FROM t_product;
-- 查询id为1
SELECT*FROM t_product WHERE id=1;
-- 模糊查询pname带有机的内容
SELECT*FROM t_product WHERE pname LIKE '%机%';
-- 删除id为1的行
DELETE FROM t_product WHERE id=1;
-- 修改id为1的价格
UPDATE t_product SET price='2000' WHERE id=1;
以上是mysql数据库的一些基本操作代码，熟练使用这些代码可以筛选出数据库表格中自己想要得数据；
mysql中的聚合函数：COUNT(*) COUNT(列名) 统计行数，前者包括空属性，后者只统计有内容的行数；
SUM(列名) 求和
AVG(列名) 求平均值
MAX最大值 MIN最小值
SQL的执行顺序： 1.FROM 2.WHERE 3.GROUP BY 4.SELECT 5.HAVING 6. ORDER BY
如果返回一个值，外查询可以使用 = > <
如果返回多个值，使用in 或者 not in;
MYSQL与Java的链接：
试着将一个用户（用户id，用户名，用户密码，用户住址）的数据库表格与java链接，完成对象数据和关系数据的转换
首先建立个用户类
package project.hw0511.bean;
import java.sql.Date;
public class adminBean {
/**用户id*/
private int id;
/**用户名*/
private String accord;
/**用户密码*/
private String password;
/**生日*/
private Date birthday;
public adminBean() {
super();
// TODO Auto-generated constructor stub
}
public adminBean(String accord, String password, Date birthday) {
super();
this.accord = accord;
this.password = password;
this.birthday = birthday;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getAccord() {
return accord;
}
public void setAccord(String accord) {
this.accord = accord;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "adminBean [id=" + id + ", accord=" + accord + ", password=" + password + ", birthday=" + birthday + "]\n";
}
}
第二步：建立一个对数据库表格操作的接口
package project.hw0511.Iadmin;
import java.sql.Date;
import java.util.List;
import project.hw0511.bean.adminBean;
public interface Iadmin {
/**
* 添加用户
* @param accord 用户名
* @param pwd 用户密码
* @param birthday 生日
*/
public void add(adminBean bean);
/**
* 按用户id删除
* @param id 用户id
*/
public void del(int id);
/**
* 按用户id修改
* @param id 用户id
* @return 用户密码
*/
public void updata(int id,String pwd);
/**
* 按用户id查找
* @param id 用户id
*/
public adminBean findById(int id);
/**
* 查询所有用户
*/
public List<adminBean> findAll();
/**
* 按用户名模糊查询
* @param accord 用户名
*/
public List<adminBean> findByAccord(String accord);
/**
* 按用户生日查询
* @param birthday 生日
*/
public List<adminBean> findByBirthday(Date startDate,Date endDate);
public boolean login(String accord,String pwd);
}
最后：建立操作的实体类链接接口，完成对数据库具体操作
package project.hw0511.adminImpl;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import project.hw0511.Iadmin.Iadmin;
import project.hw0511.bean.adminBean;
public class adminImpl implements Iadmin {
public static void main(String[] args) {
adminImpl dao = new adminImpl();
// dao.add(new adminBean("莱昂纳德", "666666", Date.valueOf("1999-12-12")));
// dao.del(1);
// dao.updata(1, "900611");
// adminBean a = dao.findById(1);
// System.out.println(a);
// List<adminBean> list=dao.findAll();
// System.out.println(list);
// List<adminBean> list=dao.findByAccord("吉");
// System.out.println(list);
// List<adminBean> list=dao.findByBirthday(Date.valueOf("1990-01-01"),
// Date.valueOf("2007-12-31"));
// System.out.println(list);
boolean log = dao.login("波波维奇", "222222");
System.out.println(log);
}
@Override
public void add(adminBean bean) {
Connection con = null;
PreparedStatement ps = null;
try {
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/admin?characterEncoding=utf-8", "root",
"zxiaoyuer");
ps = con.prepareStatement("insert into t_user(u_accord,u_password,u_birthday)values(?,?,?)");
ps.setString(1, bean.getAccord());
ps.setString(2, bean.getPassword());
ps.setDate(3, bean.getBirthday());
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public void del(int id) {
Connection con = null;
PreparedStatement ps = null;
try {
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/admin?characterEncoding=utf-8", "root",
"zxiaoyuer");
ps = con.prepareStatement("DELETE FROM t_user WHERE id=?");
ps.setInt(1, id);
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public void updata(int id, String pwd) {
Connection con = null;
PreparedStatement ps = null;
try {
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/admin?characterEncoding=utf-8", "root",
"zxiaoyuer");
ps = con.prepareStatement("UPDATE t_user SET u_password=? WHERE id=?;");
ps.setString(1, pwd);
ps.setInt(2, id);
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Override
public adminBean findById(int id) {
adminBean bean = new adminBean();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/admin?characterEncoding=utf-8", "root",
"zxiaoyuer");
ps = con.prepareStatement("SELECT*FROM t_user WHERE id=?");
ps.setInt(1, id);
rs = ps.executeQuery();
if (rs.next()) {
bean.setId(rs.getInt("id"));
bean.setAccord(rs.getString("u_accord"));
bean.setPassword(rs.getString("u_password"));
bean.setBirthday(rs.getDate("u_birthday"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return bean;
}
@Override
public List<adminBean> findAll() {
List<adminBean> list = new ArrayList<>();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/admin?characterEncoding=utf-8", "root",
"zxiaoyuer");
ps = con.prepareStatement("SELECT*FROM t_user");
rs = ps.executeQuery();
while (rs.next()) {
adminBean bean = new adminBean();
bean.setId(rs.getInt("id"));
bean.setAccord(rs.getString("u_accord"));
bean.setPassword(rs.getString("u_password"));
bean.setBirthday(rs.getDate("u_birthday"));
list.add(bean);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
@Override
public List<adminBean> findByAccord(String accord) {
List<adminBean> list = new ArrayList<>();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/admin?characterEncoding=utf-8", "root",
"zxiaoyuer");
ps = con.prepareStatement("SELECT*FROM t_user WHERE u_accord LIKE ?");
ps.setString(1, "%" + accord + "%");
rs = ps.executeQuery();
while (rs.next()) {
adminBean bean = new adminBean();
bean.setId(rs.getInt("id"));
bean.setAccord(rs.getString("u_accord"));
bean.setPassword(rs.getString("u_password"));
bean.setBirthday(rs.getDate("u_birthday"));
list.add(bean);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
@Override
public List<adminBean> findByBirthday(Date startDate, Date endDate) {
List<adminBean> list = new ArrayList<>();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/admin?characterEncoding=utf-8", "root",
"zxiaoyuer");
ps = con.prepareStatement("SELECT*FROM t_user WHERE u_birthday>=? AND u_birthday<=?");
ps.setDate(1, startDate);
ps.setDate(2, endDate);
rs = ps.executeQuery();
while (rs.next()) {
adminBean bean = new adminBean();
bean.setId(rs.getInt("id"));
bean.setAccord(rs.getString("u_accord"));
bean.setPassword(rs.getString("u_password"));
bean.setBirthday(rs.getDate("u_birthday"));
list.add(bean);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
@Override
public boolean login(String accord, String pwd) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
Class.forName("org.gjt.mm.mysql.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/admin?characterEncoding=utf-8", "root",
"zxiaoyuer");
ps = con.prepareStatement("SELECT u_password FROM t_user WHERE u_accord=?");
ps.setString(1, accord);
rs = ps.executeQuery();
if (rs.next()) {
if (rs.getString("u_password").equals(pwd)) {
return true;
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
rs.close();
ps.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
}
需要注意的是：
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/admin?characterEncoding=utf-8", "root",
"zxiaoyuer");
链接建立是，地址是否正确
ps = con.prepareStatement("SELECT u_password FROM t_user WHERE u_accord=?");
ps中的语句可以看成要在数据库中执行语句，建议在数据库中验证语句可用性，直接复制即可
ResultSet：结果集，只有在需要java接受或者显示实，需要结果集接受，和传递
