尚硅谷之JDBC

@Test

public void testConnection4()throws Exception{

Properties pro = new Properties();

pro.load(ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties"));

String url = pro.getProperty("url");

//my.ini中max_connections=10

for (int i = 0; i < 15; i++) {

Connection conn = DriverManager.getConnection(url,pro);

System.out.println(conn);

//没有关闭，资源一直没有释放

}

}

package com.atguigu.statement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import org.junit.Test;

/*

 * 网络编程：tcp

 *

 * 服务器端：

 * 1、ServerSocket server = new ServerSocket(3306);

 * 2、Socket socket = server.accept();

 * 3、InputStream input = socket.getInputStream();//接收sql，客户端传过来的

 * 4、在服务器执行sql

 * 5、把结果给客户端

 *

 * 客户端：

 * 1、Socket socket = new Socket(服务器的IP地址，3306);

 * 2、传sql

 * 3、OutputStream out = socket.getOutputStream();

 * 4、out.write(sql);

 * 5、接收结果

 * 6、断开连接  out.close();socket.close();

 */

public class TestStatement {

@Test

public void testAdd()throws Exception{

String sql = "INSERT INTO dept(dname,description) VALUES('财务部','负责发钱工作')";

String url = "jdbc:mysql://localhost:3306/1221db";

String user = "root";

String password = "123456";

Connection conn = DriverManager.getConnection(url, user, password);

Statement st = conn.createStatement();

int len = st.executeUpdate(sql);//把insert,update,delete都用这个方法

if(len>0){

System.out.println("添加成功");

}else{

System.out.println("添加失败");

}

st.close();

conn.close();

}

@Test

public void testUpdate()throws Exception{

String sql = "UPDATE dept SET description = '负责发工资、社保、公积金工作' WHERE dname ='财务部'";

String url = "jdbc:mysql://localhost:3306/1221db";

String user = "root";

String password = "123456";

Connection conn = DriverManager.getConnection(url, user, password);

Statement st = conn.createStatement();

int len = st.executeUpdate(sql);//把insert,update,delete都用这个方法

if(len>0){

System.out.println("修改成功");

}else{

System.out.println("修改失败");

}

st.close();

conn.close();

}

@Test

public void testDelete()throws Exception{

String sql = "DELETE FROM dept WHERE did =2";

String url = "jdbc:mysql://localhost:3306/1221db";

String user = "root";

String password = "123456";

Connection conn = DriverManager.getConnection(url, user, password);

Statement st = conn.createStatement();

int len = st.executeUpdate(sql);//把insert,update,delete都用这个方法

if(len>0){

System.out.println("删除成功");

}else{

System.out.println("删除失败");

}

st.close();

conn.close();

}

@Test

public void testSelect()throws Exception{

String sql = "SELECT * FROM dept";

String url = "jdbc:mysql://localhost:3306/1221db";

String user = "root";

String password = "123456";

Connection conn = DriverManager.getConnection(url, user, password);

Statement st = conn.createStatement();

ResultSet rs = st.executeQuery(sql);//select语句用query方法

while(rs.next()){//是否有下一行

//取这一行的单元格

int id = rs.getInt(1);

String name = rs.getString(2);

String desc = rs.getString(3);

System.out.println(id+"\t" + name + "\t" + desc);

}

rs.close();

st.close();

conn.close();

}

@Test

public void testSelect2()throws Exception{

String sql = "SELECT did,dname FROM dept";

String url = "jdbc:mysql://localhost:3306/1221db";

String user = "root";

String password = "123456";

Connection conn = DriverManager.getConnection(url, user, password);

Statement st = conn.createStatement();

ResultSet rs = st.executeQuery(sql);//select语句用query方法

while(rs.next()){//是否有下一行

//取这一行的单元格

int id = rs.getInt("did");

String name = rs.getString("dname");

System.out.println(id+"\t" + name);

}

rs.close();

st.close();

conn.close();

}

}