一、MySQL服务开启和关闭
!!首先:以管理员的身份打开cmd窗口。
(一)查看mysql运行情况
在cmd中执行以下语句,来打开服务窗口。
services.msc
(二)打开mysql服务
在cmd窗口中输入net start mysql
。
(三)关闭mysql服务
在cmd窗口中输入net stop mysql
。
二、mysql登录和退出
(一)登录mysql
1. 连接本地数据库:
mysql -uroot -p密码
2. 连接同一局域网内另一台电脑的数据库:
mysql -h目标主机ip -uroot -p目标数据库密码
或
mysql --host=目标主机ip --user=root --password=目标数据库密码
3. 忘记密码怎么办?
——(传送门)https://blog.csdn.net/Lilylxl/article/details/107430646
(二)退出mysql
exit
或
quit
三、JDBC连接、操作数据库
public class JdbcDemo1 {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/db1","root","root");
String sql="update stu set name='liming' where id=10";
Statement stmt = con.createStatement();
int count = stmt.executeUpdate(sql);
System.out.println(count);
stmt.close();
con.close();
}
}
(一)详解各个对象:
1.DriverManager:驱动管理对象
2.Connection数据库连接对象
3.执行sql的对象
4.ResultSet结果集对象
5.PreparedStatement执行sql的对象(预编译的sql)
(二)JDBCUtils数据库连接工具类编写
public class JdbcUtils {
private static String url;
private static String user;
private static String password;
private static String driver;
static {
try {
Properties prop=new Properties();
// prop.load(new FileReader("/src/jdbc.properties"));
ClassLoader classLoader = JdbcUtils.class.getClassLoader();
URL resource = classLoader.getResource("jdbc.properties");
String path = resource.getPath();
// System.out.println(path);
prop.load(new FileReader(path));
url=prop.getProperty("url");
user=prop.getProperty("user");
password=prop.getProperty("password");
driver=prop.getProperty("driver");
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public static void close(Statement stmt,Connection conn){
if(stmt!=null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void close(ResultSet rs,Statement stmt, Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
properties文件编写:
url=jdbc:mysql://localhost:3306/db1
user=root
password=root
driver=com.mysql.jdbc.Driver
调用JDBCUtils工具类的方法:
conn= JdbcUtils.getConnection();
JdbcUtils.close(rs,stmt,conn);
(三)事务管理
Connection conn=null;
PreparedStatement pstmt1=null,pstmt2=null;
try {
conn = JdbcUtils.getConnection();
// 开启事务
conn.setAutoCommit(false);
String sql1="update stu set grade=grade-? where id=?";
String sql2="update stu set grade=grade+? where id=?";
pstmt1 = conn.prepareStatement(sql1);
pstmt2 = conn.prepareStatement(sql2);
pstmt1.setInt(1,50);
pstmt1.setInt(2,1);
pstmt2.setInt(1,20);
pstmt2.setInt(2,2);
pstmt1.executeUpdate();
int i=3/0;
pstmt2.executeUpdate();
// 提交事务
conn.commit();
} catch (SQLException throwables) {
try {
if(conn!=null)
// 事务回滚
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
} finally {
JdbcUtils.close(pstmt1,conn);
JdbcUtils.close(pstmt2,null);
}
四、数据库连接池
(一) C3P0
(二) Druid
1. Druid工具类:
public class JdbcUtils2 {
private static DataSource ds;
static {
// 加载配置文件
try {
Properties prop=new Properties();
prop.load(JdbcUtils2.class.getClassLoader().getResourceAsStream("druid.properties"));
ds= DruidDataSourceFactory.createDataSource(prop);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
// 获取连接
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
// 释放资源
public static void close(Statement stmt, Connection conn){
close(null,stmt,conn);
}
// 释放资源
public static void close(ResultSet rs,Statement stmt, Connection conn){
if(rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static DataSource getDataSource(){
return ds;
}
}
2. 调用Druid工具类
Connection conn=null;
PreparedStatement pstmt=null;
try {
conn = JdbcUtils2.getConnection();
String sql="insert into stu(id,name,class_num,grade,teacher) values(null,?,?,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"王五");
pstmt.setInt(2,1);
pstmt.setInt(3,90);
pstmt.setInt(4,2);
int count=pstmt.executeUpdate();
System.out.println(count);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils2.close(pstmt,conn);
}
五、JDBCTemplate
public class JdbcTemplateDemo2 {
private JdbcTemplate template=new JdbcTemplate(JdbcUtils2.getDataSource());
/**
* 修改1号数据的grade的值为99
*/
@Test
public void test1(){
int count=template.update("update stu set grade=99 where id=1");
System.out.println(count);
}
/**
* 添加一条记录
*/
@Test
public void test2(){
int count = template.update("insert into stu(id,name,class_num,grade,teacher) values(null,?,?,?,?)", "lisi", 1, 122, 2);
System.out.println(count);
}
/**
* 删除刚才添加的记录
*/
@Test
public void test3(){
int count=template.update("delete from stu where name=?","lisi");
System.out.println(count);
}
/**
* 查询id为1的记录,并将其封装为Map集合
* QueryForMap:只能查询一条记录
*/
@Test
public void test4(){
Map<String, Object> map = template.queryForMap("select * from stu where id=?", 1);
System.out.println(map);
}
/**
* 查询所有的记录,并将其封装为List
*/
@Test
public void test5(){
String sql="select * from stu";
List<Map<String, Object>> list = template.queryForList(sql);
for (Map<String, Object> map : list) {
System.out.println(map);
}
}
/**
* 查询所有记录,并封装为Stu对象的List集合,
*/
@Test
public void test6(){
String sql="select * from stu";
List<Stu> list = template.query(sql, new BeanPropertyRowMapper<Stu>(Stu.class));
for (Stu stu : list) {
System.out.println(stu);
}
}
/**
* 这个是自己实现的版本,比较麻烦!
* 查询所有记录,并封装为Stu对象的List集合,
*/
@Test
public void test6_2(){
String sql="select * from stu";
List<Stu> list = template.query(sql, new RowMapper<Stu>() {
@Override
public Stu mapRow(ResultSet rs, int i) throws SQLException {
Stu stu = new Stu();
int id = rs.getInt("id");
String name = rs.getString("name");
int class_num = rs.getInt("class_num");
int grade = rs.getInt("grade");
int teacher = rs.getInt("teacher");
stu.setId(id);
stu.setName(name);
stu.setClass_num(class_num);
stu.setGrade(grade);
stu.setTeacher(teacher);
return stu;
}
});
for (Stu stu : list) {
System.out.println(stu);
}
}
/**
* 查询所有记录的个数
*/
@Test
public void test7(){
String sql="select count(*) from stu";
Long count = template.queryForObject(sql, Long.class);
System.out.println(count);
}