java连接数据库的增删改查

1.准备工作

首先下载驱动加到myeclipse的lib下，然后bulidpath添加进去，然后将该驱动加到tomcat的lib下面。

2.建立连接

//连接数据库
		Connection c = null;
		try {
			Class.forName("org.postgresql.Driver");
			c = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/postgres", "postgres",
					"123456");
		} catch (Exception e) {
			e.printStackTrace();
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		System.out.println("Opened database successfully") ;

3.执行语句

3.1创建数据表

 public static void main( String args[] )
     {
       Connection c = null;
       Statement stmt = null;
       try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "CREATE TABLE COMPANY " +
                      "(ID INT PRIMARY KEY     NOT NULL," +
                      " NAME           TEXT    NOT NULL, " +
                      " AGE            INT     NOT NULL, " +
                      " ADDRESS        CHAR(50), " +
                      " SALARY         REAL)";
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
       } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
       }
       System.out.println("Table created successfully");
     }

3.2增加数据到数据表

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
               + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
               + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
               + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
               + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
         stmt.executeUpdate(sql);

         stmt.close();
         c.commit();
         c.close();
      } catch (Exception e) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

3.3修改数据表

public boolean addResult(String proName, String result, String time)
			throws IOException, JDOMException, SQLException {
		if (getConnection() == null) {
			System.out.println("返回值为空");
		}
		PreparedStatement ps = null;
		Connection conn = getConnection();
		ReadConfig config = new ReadConfig();
		if (config.readSql("mess", "config.xml") == null) {
			System.out.println("返回值为空");

		}
		List<MsqConnModel> list1 = config.readSql("mess", "config.xml");
		DBTABLE = list1.get(0).getDBTABLE();

		List<TestResult> lite = new ArrayList<TestResult>();
		lite = getResult();
		//
		boolean flag = false;
		String sql = null;
		for (int i = 0; i < lite.size(); i++) {

			if (lite.get(i).getProName().equals(proName)) {
				flag = true;
				break;
			}
		}
		if (flag == true) {
			sql = "UPDATE " + DBTABLE + " SET result=?,time=? WHERE proName=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, result);
			ps.setString(2, time);
			ps.setString(3, proName);
			try {

				ps.executeUpdate();

			} catch (Exception e) {

				return false;
			}
			// "update id,proName,result,time FROM " + DBTABLE;
		}
		if (flag == false) {
			sql = "INSERT INTO " + DBTABLE + " (proName,result,time)"
					+ "VALUES (?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, proName);
			ps.setString(2, result);
			ps.setString(3, time);
			try {

				ps.executeUpdate();

			} catch (Exception e) {

				return false;
			}

		}
		ps.close();
		conn.close();
		return true;
	}

3.4查询数据信息

Statement stmt = null; // 数据库的操作对象
		ResultSet rs = null; // 保存查询结果
		String sql = "SELECT id,proName,result,time FROM " + DBTABLE;
		stmt = conn.createStatement();
		rs = stmt.executeQuery(sql);
		List<TestResult> list = new ArrayList<TestResult>();
		if (rs.next()) {
			System.out.println("rs has data");
		} else {
			System.out.println("rs has NO data");
		}
		rs.beforeFirst();
		while (rs.next()) { // 依次取出数据
			TestResult tsrl = new TestResult();

			String ids = String.valueOf(rs.getInt(1));
			tsrl.setId(ids);
			tsrl.setProName(rs.getString(2));
			tsrl.setResult(rs.getString(3));
			tsrl.setTime(rs.getString(4));
			list.add(tsrl);
		}

		rs.close();
		stmt.close();
		conn.close();

3.5删除数据表中的数据

4.得到结果