postgresql实践

今天花了2个小时学习了下postgresql，以下为学习内容：

文档：http://www.php100.com/manual/PostgreSQL8/
入门blog：http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html (非常适合新手学习使用)
pg安装及配置：http://blog.csdn.net/saloon_yuan/article/details/7904496

1.安装配置postgresql
详见：http://blog.csdn.net/saloon_yuan/article/details/7904496
yum install postgresql-tcl postgresql-server postgresql-contrib postgresql
su - postgres （初次安装后，默认新建了一个postgres Linux用户，postgres数据库用户以及postgres数据库）
initdb –encoding=UTF-8 –no-locale -D /var/lib/pgsql/data/
psql -l
sudo yum install pgadmin3

2.pg基本命令以及CRUD

    \h：查看SQL命令的解释，比如\h select。
    \?：查看psql命令列表。
    \l：列出所有数据库。
    \c [database_name]：连接其他数据库。
    \d：列出当前数据库的所有表格。
    \d [table_name]：列出某一张表格的结构。
    \du：列出所有用户。
    \e：打开文本编辑器。
    \conninfo：列出当前数据库和连接的信息。 

CRUD 详见：http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html

3.使用 mybatis 对 postgres CRUD

部分参考 blog:http://blog.csdn.net/liu00614/article/details/36215939
步骤
1st. 导入mybatis以及postgres jar包
2nd. 写sqlMapConfig.xml
3rd. javaBean
4th. javaBeanSqlMap.xml

代码详见：
SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 给bean取别名用在mapping文件中 -->
    <typeAliases>
        <typeAlias type="com.travelzen.jdbc.Worker" alias="Worker" />
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <!-- 使用JDBC的事务管理 -->
            <transactionManager type="JDBC" />
            <!-- 数据源 -->
            <dataSource type="POOLED">
                <property name="driver" value="org.postgresql.Driver" />
                <property name="url" value="jdbc:postgresql://127.0.0.1:5432/postgres" />
                <property name="username" value="postgres" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>
    <!-- 这里可以写多个实体的映射文件 -->
    <mappers>
        <mapper resource="com/travelzen/jdbc/WorkerSqlMap.xml" />
    </mappers>
</configuration>

javaBean : Worker.java

package com.travelzen.jdbc;

public class Worker {  
    private String id;  
    private String name;  
    int age;  
    public Worker() {  
        super();  
    }  
    public Worker(String id, String name, int age) {  
        super();  
        this.id = id;  
        this.name = name;  
        this.age = age;  
    }  
    public String getId() {  
        return id;  
    }  
    public void setId(String id) {  
        this.id = id;  
    }  
    public String getName() {  
        return name;  
    }  
    public void setName(String name) {  
        this.name = name;  
    }  
    public int getAge() {  
        return age;  
    }  
    public void setAge(int age) {  
        this.age = age;  
    }  
    @Override  
    public String toString() {  
        return "Worker [id=" + id + ", name=" + name + ", age=" + age + "]";  
    }  
}

WorkerSqlMap.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- namespace用于java代码调用时识别指定xml的mapper文件 -->
<mapper namespace="com.travelzen.jdbc">
    <sql id="field">
        id,name,age
    </sql>
    <select id="getAllWorker" resultType="Worker">
        select
        <include refid="field" />
        from worker
    </select>
    <select id="getWorkerById" resultType="Worker">
        select
        <include refid="field" />
        from worker where
        id= #{id}
    </select>
    <select id="getWorkerByName" resultType="Worker">
        select
        <include refid="field" />
        from worker where name like '%'||#{name}||'%'
    </select>

    <insert id="addWorker">
        insert into worker (
        <include refid="field" />
        ) values(#{id},#{name},#{age})
    </insert>

    <update id="updateWorker">
        update worker set name=#{name} ,age=#{age} where
        id=#{id}
    </update>

    <delete id="deleteWorkerById">
    delete from worker where id=#{id};
    </delete>
</mapper>

Test.java

package com.travelzen.test;
import java.io.IOException;
import java.io.Reader;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.travelzen.jdbc.Worker;


public class Test {
    public static void main(String[] args) {
        SqlSessionFactory factory = null;
        try {
            //Reader reader = Resources.getResourceAsReader("com/travelzen/jdbc/SqlMapConfig.xml");
            Reader reader = Resources.getResourceAsReader("com/travelzen/jdbc/SqlMapConfig.xml");
            factory = new SqlSessionFactoryBuilder().build(reader);

        } catch (IOException e) {
            e.printStackTrace();
        }

        SqlSession session = factory.openSession();

        Worker worker = new Worker("003", "Join", 100);
        //session.insert("com.travelzen.jdbc.addWorker", worker);
        //session.delete("com.travelzen.jdbc.deleteWorkerById", "002");
        //session.commit();
        List<Worker> list = session.selectList("com.travelzen.jdbc.getAllWorker");
        System.out.println(session.selectOne("com.travelzen.jdbc.getWorkerById", "001"));
        System.out.println(session.update("com.travelzen.jdbc.updateWorker", worker));
        System.out.println(session.delete("com.travelzen.jdbc.deleteWorkerById", "004"));
        session.commit();
        System.out.println(session.selectList("com.travelzen.jdbc.getWorkerByName", "J"));
        System.out.println(list);
    }

}package com.travelzen.test;
import java.io.IOException;
import java.io.Reader;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.travelzen.jdbc.Worker;


public class Test {
    public static void main(String[] args) {
        SqlSessionFactory factory = null;
        try {
            //Reader reader = Resources.getResourceAsReader("com/travelzen/jdbc/SqlMapConfig.xml");
            Reader reader = Resources.getResourceAsReader("com/travelzen/jdbc/SqlMapConfig.xml");
            factory = new SqlSessionFactoryBuilder().build(reader);

        } catch (IOException e) {
            e.printStackTrace();
        }

        SqlSession session = factory.openSession();

        Worker worker = new Worker("003", "Join", 100);
        //session.insert("com.travelzen.jdbc.addWorker", worker);
        //session.delete("com.travelzen.jdbc.deleteWorkerById", "002");
        //session.commit();
        List<Worker> list = session.selectList("com.travelzen.jdbc.getAllWorker");
        System.out.println(session.selectOne("com.travelzen.jdbc.getWorkerById", "001"));
        System.out.println(session.update("com.travelzen.jdbc.updateWorker", worker));
        System.out.println(session.delete("com.travelzen.jdbc.deleteWorkerById", "004"));
        session.commit();
        System.out.println(session.selectList("com.travelzen.jdbc.getWorkerByName", "J"));
        System.out.println(list);
    }

}