今天花了2个小时学习了下postgresql，以下为学习内容：
文档：http://www.php100.com/manual/PostgreSQL8/
入门blog：http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html (非常适合新手学习使用)
pg安装及配置：http://www.jb51.cc/article/p-artzrvgm-bek.html
1.安装配置postgresql
详见：http://www.jb51.cc/article/p-artzrvgm-bek.html
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://www.jb51.cc/article/p-emwtosjo-bkm.html
步骤
1st. 导入mybatis以及postgres jar包
2nd. 写sqlMapConfig.xml
3rd. javaBean
4th. javaBeansqlMap.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","J"));
System.out.println(list);
}
}
